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

 

[Michael M]

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

Well, I read the whole post for nothing.....'cause I'm still non the wiser !!
It might be simpler if you provide a sample of data with before and after processing.
I know you posted some numbers

6 05900911 6-59-911
4 21700034 4-217-034
7 02000202 7-20-202

but don't follow, as you said one of the numbers should read 34 and not 034 !!

 

[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

Does this formula do what you want...

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

 

[Michael M]

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

Playing with the small sample you provided, try this code

Sub MM1()
Dim lr As Long
lr = Cells.Find("*", , xlValues, , xlRows, xlPrevious).Row
With Range("A1:A" & lr)
    .Replace " 0", "-"
    .Replace "000", "0-"
    .Replace "00", "-"
 End With
End Sub

 

[Michael M]

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

Dang Rick, you always make it seem so easy....ray:

 

[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

Dang Rick, you always make it seem so easy....ray:

Thanks for the kind words. If the OP wanted to do this with a macro, here is another one he can consider (it is built upon the formula I posted in Message #3 )...

[table="width: 500"]
[tr]
	[td]Sub FixWackyValues()
  With Range("A1", Cells(Rows.Count, "A").End(xlUp))
    .Value = Evaluate(Replace("IF({1},LEFT(@)&""-""&0+MID(@,3,3)&""-""&0+RIGHT(@,4))", "@", .Address))
  End With
End Sub[/td]
[/tr]
[/table]

 

[Michael M]

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

It will be interesting to see if the dataset follows the patterns he earlier mentioned !!

 

[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

Hi Michael M,

Thanks for the reply , you're right there should be no leading zeros. 4 21700034 should be 4-217-34

With the formula i used, i was able to remove from the last set of four digits, the first character if it was a zero, but was not able to do so in case the second character was a zero as well, or the third.

4-217-034" should in fact be "4-217-34".

 

[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

Hi Rick, thanks for the reply. Bulls Eye! You knocked it out of the park there buddy!

May i ask how this sorcery works?

I see you placed a 0 in front of the Mid formula which excludes leading zeroes? Or how is the logic accounting to exclude them?

Thanks Rick, really made it seem so simple.

"Does this formula do what you want...

=LEFT(A3)&"-"&0+MID(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

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"]
<colgroup><col style="width: 91px"><col width="74"><col width="135"><col width="60"></colgroup><tbody>[TR]
[TD="bgcolor: #F3F3F3"]ID[/TD]
[TD="bgcolor: #F3F3F3"]IF[/TD]
[TD="bgcolor: #ead1dc"]Rick[/TD]
[TD="bgcolor: #f3f3f3"]Correct[/TD]
[/TR]
[TR]
[TD="bgcolor: #F3F3F3"]4 10302727[/TD]
[TD="bgcolor: #F3F3F3"]4- 10-272[/TD]
[TD="bgcolor: #EAD1DC"]4-10-2727[/TD]
[TD="bgcolor: #F3F3F3"]4-103-2727

[/TD]
[/TR]
[TR]
[TD="bgcolor: #F3F3F3"]4 13001353[/TD]
[TD="bgcolor: #F3F3F3"]4-130-1353[/TD]
[TD="bgcolor: #EAD1DC"]4-130-1353[/TD]
[TD="bgcolor: #FFFF00"]4-130-1353

[/TD]
[/TR]
[TR]
[TD="bgcolor: #F3F3F3"]4 15800742[/TD]
[TD="bgcolor: #F3F3F3"]4-158-742[/TD]
[TD="bgcolor: #EAD1DC"]4-158-742[/TD]
[TD="bgcolor: #FFFF00"]4-158-742

[/TD]
[/TR]
[TR]
[TD="bgcolor: #F3F3F3"]4 15800201[/TD]
[TD="bgcolor: #F3F3F3"]4-158-201[/TD]
[TD="bgcolor: #EAD1DC"]4-158-201[/TD]
[TD="bgcolor: #F3F3F3"]4-158-201

[/TD]
[/TR]
[TR]
[TD="bgcolor: #F3F3F3"]4 18700297[/TD]
[TD="bgcolor: #F3F3F3"]4-187-297[/TD]
[TD="bgcolor: #EAD1DC"]4-187-297[/TD]
[TD="bgcolor: #F3F3F3"]4-187-297

[/TD]
[/TR]
[TR]
[TD="bgcolor: #F3F3F3"]7 09301152[/TD]
[TD="bgcolor: #F3F3F3"]7-93-1152[/TD]
[TD="bgcolor: #EAD1DC"]7-93-1152[/TD]
[TD="bgcolor: #F3F3F3"]7-93-1152

[/TD]
[/TR]
</tbody>[/TABLE]