Dissecting a number string to correct format with IF AND IFS MID and getting rid of some but not all zeros

[Lou Pointspreads]

Hello Exceptional Excel Extraordinaires,

I come before you to seek your guidance with this issue i'm currently struggling with and would really like to solve and or automate.

I have two databases which i need to join or relate, been trying to do it in access but one of them has the information in a rather unusual or unfriendly format. (Please don't send me away just yet, i will keep it excel oriented i promise)

This brought me to thinking about copying the table to excel, and tinkering with a text string formula to build the correct format out of what we will call the "wacky" format from now on, in order to be able to match it to the other table with the right data.

So about the right data, its a 10 digit number separated by dashes, 1-234-5678.
The wacky table would read like this: "1 023405678" with a space following the first digit, which would need to be replaced eventually by a dash. After the space, the following three digits will carry a value from 1-999, but in the wacky format a value of 1 would read 001, a value of 10 read 010.

Here comes the kicker, after the three digits there should be a dash. However the crappy system responsible for the wacky format does not handle "-" and replaces them with zeros.

Which is then followed by the last four digits, which would be a value from 1-9999 and would have the same issue of 1 being displayed as 0001, 10 as 0010, 100 as 0100 however we don't like the zeros as place holders.

So i did some reading, and found some posts already in the forum which were helpful in figuring out how to dissect this wacky data. The winning combo was a formula with IF AND IFS, to create a logic to get rid of the zero placeholders and then a simple join using & and sticking the dashes in between. To get rid of the zeros we would use the if mixed with a MID to check if the first digit of the second set was equal to zero. If it was, onto the second character, if it was then only return the last digit and voila gone are the zeros.

The first digit was a piece of cake, with a simple left formula asking for the 1st character.
The middle three digits would need to skip the space after the first digit, and also remove preceding zeros.

This worked well with the formula below for the middle three digits, but i could only get rid of the first zero in the last four digits.

=(+LEFT(A3,1))&"-"&(+IF(AND(A3=(MID(A3,3,1)="0"),A3=(MID(A3,4,1)="0")),MID(A3,5,1),+IFS(MID(A3&" ",3,1)="0",MID(A3&" ",4,2),MID(A3&" ",3,1)<>0,MID(A3&" ",3,3))))&"-"&(+IF(AND(A3=(MID(A3,7,1)="0"),A3=(MID(A3,8,1)="0")),MID(A3,9,2),+IFS(MID(A3&" ",7,1)="0",MID(A3&" ",8,3),MID(A3&" ",7,1)<>0,MID(A3&" ",7,4))))

Where A3 would be the number in the wacky format we are trying to convert, in order to be able to match it to the table with the correct format.[TABLE="class: cms_table, width: 500"]
<tbody>[TR]
[TD]4 13001353[/TD]
[TD]=(+LEFT(A3,1))&"-"&(+IF(AND(A3=(MID(A3,3,1)="0"),A3=(MID(A3,4,1)="0")),MID(A3,5,1),+IFS(MID(A3&" ",3,1)="0",MID(A3&" ",4,2),MID(A3&" ",3,1)<>0,MID(A3&" ",3,3))))&"-"&(+IF(AND(A3=(MID(A3,7,1)="0"),A3=(MID(A3,8,1)="0")),MID(A3,9,2),+IFS(MID(A3&" ",7,1)="0",MID(A3&" ",8,3),MID(A3&" ",7,1)<>0,MID(A3&" ",7,4))))[/TD]
[/TR]
</tbody>[/TABLE]


The correct format for this would be 4-130-1353

Out of the second row example below, we were able to get rid of the first zero out of the last four digits, but the third digit was a zero as well, ideally it should read 34 instead of 034. And a 1 would read 0001 in the wacky format. "NO BUENO"

[TABLE="width: 500"]
<tbody>[TR]
[TD]<style type="text/css"><!--td {border: 1px solid #ccc;}br {mso-data-placement:same-cell;}--></style>6 05900911[/TD]
[TD]<style type="text/css"><!--td {border: 1px solid #ccc;}br {mso-data-placement:same-cell;}--></style>6-59-911[/TD]
[/TR]
[TR]
[TD]<style type="text/css"><!--td {border: 1px solid #ccc;}br {mso-data-placement:same-cell;}--></style>4 21700034[/TD]
[TD]<style type="text/css"><!--td {border: 1px solid #ccc;}br {mso-data-placement:same-cell;}--></style>4-217-034[/TD]
[/TR]
[TR]
[TD]<style type="text/css"><!--td {border: 1px solid #ccc;}br {mso-data-placement:same-cell;}--></style>7 02000202[/TD]
[TD]<style type="text/css"><!--td {border: 1px solid #ccc;}br {mso-data-placement:same-cell;}--></style>7-20-202[/TD]
[/TR]
</tbody>[/TABLE]

I need to add an extra two layers to the part of the IFS formula that handles getting rid of the first zero out of the four last digits to be able to get rid of up to three place holding zeros.

Any thoughts, tips or links to recommended readings on how to accomplish this?

Ideally this concepts should be translatable to access where it could generate a new column within the wacky table automatically that would have the correct format and allow a join with the correct table.

I know i promised to keep this strictly excel, but just had to throw this out there for context.

Thanks for the read if you made it this far!

Cheers,

Lou

 

[Rick Rothstein]

Re: Help Dissecting a number string to correct format with IF AND IFS MID and getting rid of some but not all zeros

Spoke too soon Rick, I found an example where it did not work. The first example here, 4 10302727 which should read 4-103-2727 is being returned 4-10-2727.

<style type="text/css"><!--td {border: 1px solid #ccc;}br {mso-data-placement:same-cell;}--></style>[TABLE="width: 0"]
<tbody>[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F3F3F3]#F3F3F3[/URL] "]ID[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F3F3F3]#F3F3F3[/URL] "]IF[/TD]
[TD="bgcolor: #ead1dc"]Rick[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F3F3F3]#F3F3F3[/URL] "]Correct[/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F3F3F3]#F3F3F3[/URL] "]4 10302727[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F3F3F3]#F3F3F3[/URL] "]4- 10-272[/TD]
[TD="bgcolor: #EAD1DC"]4-10-2727[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F3F3F3]#F3F3F3[/URL] "]4-103-2727
[/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F3F3F3]#F3F3F3[/URL] "]

[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F3F3F3]#F3F3F3[/URL] "][/TD]
[TD="bgcolor: #EAD1DC"][/TD]
[TD="bgcolor: #FFFF00"][/TD]
[/TR]
</tbody>[/TABLE]

Off the top of my head, the only way I can think that would happen is if your had two blank spaces after the 4 instead of one. If that is a possibility (as opposed to a mistake in your test), then try this formula...

=LEFT(A3)&"-"&0+MID(TRIM(A3),3,3)&"-"&0+RIGHT(A3,4)

 

[Lou Pointspreads]

Re: Help Dissecting a number string to correct format with IF AND IFS MID and getting rid of some but not all zeros

You may be right about the two spaces, let me get back to you on that. Thanks for the heads up.

 

[Lou Pointspreads]

Re: Help Dissecting a number string to correct format with IF AND IFS MID and getting rid of some but not all zeros

You nailed it Rick!

There was a double space from a line break just on the first line.

May i ask the effect of placing a 0 in front of a formula? how does the logic go in getting rid of the leading zeros?

Thanks Rick!

 

[Rick Rothstein]

Re: Help Dissecting a number string to correct format with IF AND IFS MID and getting rid of some but not all zeros

May i ask the effect of placing a 0 in front of a formula? how does the logic go in getting rid of the leading zeros?

In Excel, when you have a text number (that is what text function like RIGHT, MID, LEFT, SUBSTITUTE, REPLACE, etc. return when they return what looks like a number)... if you involve it in a mathematical expression, Excel will convert the text number into a real number in order to perform the math. Adding 0 to anything will not change its value so adding 0 to a text number makes it into a real number without changing its numerical value in any way. Since real numbers do not have leading zeros, what I did returns just the number part as you requested.

 

[Lou Pointspreads]

Re: Help Dissecting a number string to correct format with IF AND IFS MID and getting rid of some but not all zeros

You are AWESOME Sir!!

Thanks for the lesson.

Good luck to ya Rick