Truncating: Mid, Left, Right, & Find.....Help!

**rturnbow** · 04-05-2013, 07:41 PM

I have a large quantity of data that comes in the format shown in Column A. I'd like to separate that content in columns only containing the numbers. I've made a few attempts with mid, right, left, find, & text to column but no luck. I continue to come up just a bit short (still get dashes, catch a W or E, etc) due to the inconsistency in the way the data is entered. Can anyone tell me a formula to get the numbers isolated? Thanks for all your help!

Excel Help.JPG

**Richard Buttrey** · 04-05-2013, 07:43 PM

Please upload a workbook or a representative cut down copy, anonymised if necessary. It is always easier to advise if we can see your request in its context.

Please don't upload pictures. None of us are inclined to recreate your data when you have a workbook available - see guidelines in the Rules area of this forum.

**djapigo** · 04-05-2013, 08:02 PM

It's not pretty, but here are my formulas...

If your data is in A1, then put these in...
B1: =MID(A1,6,SEARCH("-",A1)-6)
C1: =MID(A1,SEARCH("-",A1)+1,SEARCH("-",A1,SEARCH("-",A1)+1)-SEARCH("-",A1)-2)
D1: =MID(A1,SEARCH("-",A1,SEARCH("-",A1)+1)+1,LEN(A1)-SEARCH("-",A1,SEARCH("-",A1)+1)-1)