Separate out a text string in one cell, and break into into individual cells

[StephenHall]

Attachment 189810

What I am trying to achieve is the following:- I have the text string 10"-A1L-FG-21149-01 in cell B5, and
wish to separate this into its individual components using a formula to cells F5,G5,H5,I5,J5.

i.e
F5=10”
G5=A1L
H5=FG
I5=21149
J5=01

Please note that F5 and H5 are not always two digits i.e F5 may be 8” for example and H5 could consist of
no more than 3 digits i.e MFG.

Would appreciate a method which would best do this by Formula, i tried with amacro but this became to complex
as the list is filtered and rows are non consecutive.

Regards

Steve

PS:=please refer to attachement

[dogberry]

Hi

Why not use text to columns on the data tab and use the - as your splitter.

Chris

[brynbaker]

Im pretty sure there is no direct formula to do this, (and a user defined formula is limited to changing only the cell containing its call). And here is the reason; the VBA recorded to do a text to columns (for that is what it is called) is as follows.

Please Login or Register  to view this content.

So back to macro. They're not so bad! You could run down the fitered column checking if the cell concerned is visble and if so, doing the necessary similar to the above).

Please Login or Register  to view this content.

Wrap the whole in a suitable loop.

[FDibbins]

ok i think i sorted it out for you. its ugly as heck, but give it a shot. assuming your data is in A1...

F5=10”=LEFT(A1,SEARCH("""",A1,1))

G5=A1L=MID(A1,SEARCH("-",A1,1)+1,SEARCH("-",A1,SEARCH("-",A1,1)+1)-1-SEARCH("-",A1,1))

H5=FG=MID(A1,SEARCH("-",A1,SEARCH("-",A1,1)+1)+1,SEARCH("-",A1,SEARCH("-",A1,SEARCH("-",A1,1)+1)+1)-SEARCH("-",A1,SEARCH("-",A1,1)+1)-1)

I5=21149=MID(A1,SEARCH("-",A1,SEARCH("-",A1,SEARCH("-",A1,1)+1)+1)+1,SEARCH("-",A1,SEARCH("-",A1,SEARCH("-",A1,SEARCH("-",A1,1)+1)+1)+1)-SEARCH("-",A1,SEARCH("-",A1,SEARCH("-",A1,1)+1)+1)-1)

J5=01=RIGHT(A1,LEN(A1)-SEARCH("-",A1,SEARCH("-",A1,SEARCH("-",A1,SEARCH("-",A1,1)+1)+1)+1))

let me know how you make out?

[benishiryo]

hi Steve, welcome to the forum. an alternative:
=TRIM(MID(SUBSTITUTE($B$5,"-",REPT(" ",50)),1+(COLUMNS($F$5:F5)-1)*50,50))

copy across

[StephenHall]

Sorry for not getting back to you sooner! this seems to work wonderfully, with a few exceptions! when i came to a cell when the configuration wasnt quite the same.

I realize that the search identifies and finds the text based on the position, but don't quite get how the parenthesis gets you to this point, which id like to understand! this is very powerful tool.

Thank you very much for your help and assistance.

regards

Steve

[StephenHall]

Didnt try this one out , but will give it a go when i get the chance!

[brynbaker]

You've not said whose offering you are working with.