Help splitting out multiple pieces of data from 1 cell (various ranges)

Excel.jpg

Hey guys,

The attached image shows an example of the data in column B that is highlighted in red that I need extrapolated in seperate columns like C, D, E, F and G shown. Main problem I'm having which goes beyond my scope is the name lengths are different sizes and uniquely identifying how to capture each of the highlighted red parts. If you think you can help me I'd greatly appreciate it!

You can use Data | Text-to-Columns to split the data at the spaces and then delete the columns that you don't need.

Hope this helps.

Pete

Thanks for replying. I thought of doing that, but I'm working with thousands of rows of data, so that would be impractical for me. I used the following formula to extract the names:

=IFERROR(MID(A2,FIND(" ",A2)+1,FIND("REG",A2)-12),MID(A2,FIND(" ",A2)+1,FIND("OVT",A2)-12))

but I also need a way to capture the 1st, 2nd and 4th pieces of numerical information in the columns next to the name extraction.

If you post an example Excel workbook rather than a picture then we might be able to work on some formulae for you - I certainly don't fancy trying to reproduce your example data before being able to play about with it.

I'm not sure why you say that Text-to-Columns is "impractical" because you have thousands of rows - it's a one-time operation, regardless of the number of rows of data.

Pete

Hmm I can't see a link to allow me to attach an excel file. Can email it to you if that sufficies.

^ Thank you for the clarifying how to attach. I wouldn't consider myself that savvy with excel, so maybe some of the options you are suggesting will help once you try it on the sample I'm attaching. I was able to on my own extract the name and 1st portion of the numbers I need, but struggling to come up with a way to capture the 2nd and 4th numerical values. Below my extraction is how the data comes out if I try to T2C the data by spaces, which from how I've done it anyway is all over the place :/

You had attached your picture to post #1, so I assumed you knew how to do it, as it is the same procedure.

Thanks to FDibbins for the detailed description.

Pete

I must be a bit slow today !!

I've put the necessary formulae to extract all the required data in the attached file, though some of them don't seem so "elegant" to me..

Hope this helps.

Pete

Originally Posted by FDibbins

great catch on the REG and OVT Pete, I saw the REG, but not the OVT, well done

Maybe that's why it took me a bit longer !! <bg>

Thanks for the rep.

Pete

Yes, it has been very slow for me for the last half-hour or so. I've sent you the file, but you should avoid putting your email address in public forums - why don't you edit it out now that I've responded?

Pete

Thanks Pete! Astounded and amazed! Going to look at your formulas later to try and make sense of them!

A few more words of explanation:

The double unary minus (--) is used to convert a string representation of a number (which is what you get with MID, RIGHT, LEFT etc.) to a proper number - essentially, it is the same as multiplying by -1 twice.

Having extracted the name and the type, these can be re-combined with spaces and then used with SUBSTITUTE to remove them from the original string, so that it is then a matter of using LEFT(string,SEARCH(" ",string)-1). This applies to the 3rd and 4th columns, but for the 5th column it is easier to use RIGHT to get the final part of the string.

Hope this helps.

Pete