Trouble with identifying the Nth "-"

[dark_rug]

Hey all,

I'm translating a string characters:

xzyxzyzxyy 8-22-60-5W6 zxyzxyyxzy
zxyxzy 10-21-1-6 yxzyzyyxyzxyyzyxy
yzxyxzyyxzyxzyxyzyx 6-20-111-5 yzx

to:

08-22-060-05W6
10-21-001-06W6
06-20-111-05W6

And I was thinking of searching for the Nth "-" by using a FIND("-",A1,FIND("-",A1)+0) tied to a TEXT(value, format) inside a MID command inside a CONCATENATE to string it all together.

I really don't have any idea how to do this though.

 

[nithin shettigar]

Try this it may work =mid(a2,search(" ",a2),search(" ",a2,search(" ",a2)+1)-search(" ",a2))

 

[mikerickson]

Something like
=FIND("~",SUBSTITUTE(A1,"/","~",N))

would find it

 

[dark_rug]

I don't understand; how would I implement the leading zeros (and everything else)?

 

[nithin shettigar]

=concatenate(text(left(mid(a2,search(" ",a2),search(" ",a2,search(" ",a2)+1)-search(" ",a2)),(search("-",mid(a2,search(" ",a2),search(" ",a2,search(" ",a2)+1)-search(" ",a2))))-1),"00"),"-",text(mid(mid(a2,search(" ",a2),search(" ",a2,search(" ",a2)+1)-search(" ",a2)),search("-",mid(a2,search(" ",a2),search(" ",a2,search(" ",a2)+1)-search(" ",a2)))+1,search("-",mid(a2,search(" ",a2),search(" ",a2,search(" ",a2)+1)-search(" ",a2)),search("-",mid(a2,search(" ",a2),search(" ",a2,search(" ",a2)+1)-search(" ",a2)))+1)-search("-",mid(a2,search(" ",a2),search(" ",a2,search(" ",a2)+1)-search(" ",a2)))-1),"00"),"-",text(substitute(mid(mid(a2,search(" ",a2),search(" ",a2,search(" ",a2)+1)-search(" ",a2)),search("-",mid(a2,search(" ",a2),search(" ",a2,search(" ",a2)+1)-search(" ",a2)),search("-",mid(a2,search(" ",a2),search(" ",a2,search(" ",a2)+1)-search(" ",a2)))+1),search("-",mid(a2,search(" ",a2),search(" ",a2,search(" ",a2)+1)-search(" ",a2)),search("-",mid(a2,search(" ",a2),search(" ",a2,search(" ",a2)+1)-search(" ",a2)),search("-",mid(a2,search(" ",a2),search(" ",a2,search(" ",a2)+1)-search(" ",a2)))+1)+1)-search("-",mid(a2,search(" ",a2),search(" ",a2,search(" ",a2)+1)-search(" ",a2)),search("-",mid(a2,search(" ",a2),search(" ",a2,search(" ",a2)+1)-search(" ",a2)))+1)),"-",""),"000"),"-",text(mid(mid(a2,search(" ",a2),search(" ",a2,search(" ",a2)+1)-search(" ",a2)),search("-",mid(a2,search(" ",a2),search(" ",a2,search(" ",a2)+1)-search(" ",a2)),search("-",mid(a2,search(" ",a2),search(" ",a2,search(" ",a2)+1)-search(" ",a2)),search("-",mid(a2,search(" ",a2),search(" ",a2,search(" ",a2)+1)-search(" ",a2)))+1)+1)+1,10),"00w6")) try if u can make something from it.

 

[dark_rug]

=concatenate(text(left(mid(a2,search(" ",a2),search(" ",a2,search(" ",a2)+1)-search(" ",a2)),(search("-",mid(a2,search(" ",a2),search(" ",a2,search(" ",a2)+1)-search(" ",a2))))-1),"00"),"-",text(mid(mid(a2,search(" ",a2),search(" ",a2,search(" ",a2)+1)-search(" ",a2)),search("-",mid(a2,search(" ",a2),search(" ",a2,search(" ",a2)+1)-search(" ",a2)))+1,search("-",mid(a2,search(" ",a2),search(" ",a2,search(" ",a2)+1)-search(" ",a2)),search("-",mid(a2,search(" ",a2),search(" ",a2,search(" ",a2)+1)-search(" ",a2)))+1)-search("-",mid(a2,search(" ",a2),search(" ",a2,search(" ",a2)+1)-search(" ",a2)))-1),"00"),"-",text(substitute(mid(mid(a2,search(" ",a2),search(" ",a2,search(" ",a2)+1)-search(" ",a2)),search("-",mid(a2,search(" ",a2),search(" ",a2,search(" ",a2)+1)-search(" ",a2)),search("-",mid(a2,search(" ",a2),search(" ",a2,search(" ",a2)+1)-search(" ",a2)))+1),search("-",mid(a2,search(" ",a2),search(" ",a2,search(" ",a2)+1)-search(" ",a2)),search("-",mid(a2,search(" ",a2),search(" ",a2,search(" ",a2)+1)-search(" ",a2)),search("-",mid(a2,search(" ",a2),search(" ",a2,search(" ",a2)+1)-search(" ",a2)))+1)+1)-search("-",mid(a2,search(" ",a2),search(" ",a2,search(" ",a2)+1)-search(" ",a2)),search("-",mid(a2,search(" ",a2),search(" ",a2,search(" ",a2)+1)-search(" ",a2)))+1)),"-",""),"000"),"-",text(mid(mid(a2,search(" ",a2),search(" ",a2,search(" ",a2)+1)-search(" ",a2)),search("-",mid(a2,search(" ",a2),search(" ",a2,search(" ",a2)+1)-search(" ",a2)),search("-",mid(a2,search(" ",a2),search(" ",a2,search(" ",a2)+1)-search(" ",a2)),search("-",mid(a2,search(" ",a2),search(" ",a2,search(" ",a2)+1)-search(" ",a2)))+1)+1)+1,10),"00w6")) try if u can make something from it.

Dude, wow. I had finally gotten it and you show up haha.
Mine's a little prettier.

=CONCATENATE(TEXT(MID(F9,FIND("-",F9,1)-2,2),"00"),"-",TEXT(MID(F9,FIND("-",F9,FIND("-",F9))+1,FIND("-",F9,FIND("-",F9)+1)-(FIND("-",F9,FIND("-",F9))+1)),"00"),"-",TEXT(MID(F9,1+FIND("-",F9,FIND("-",F9)+1),FIND("-",F9,FIND("-",F9,FIND("-",F9)+1)+1)-FIND("-",F9,FIND("-",F9)+1)-1),"000"),"-",TEXT(MID(F9,FIND("-",F9,FIND("-",F9,FIND("-",F9)+1)+1)+1,1),"00"),"W6")