Extract text and number from a long text

Hello,

I don't know how to extract the exact number and text in this long text. For example:

29. 26. 1683511. 8. U XMAS LG SQUATTER CLIPSTRIP
29. 31. 3080103. 9. A DOGLOO KD LG DOG HOUSE 25951
29. 26. 29554811. 9. A BOODA FRSH/FLOSS TOY MEDIUM SPRMNT

Anything that I hilighted I want to be able to have them in seperate columns. Please help and thank you.

Please change your highlight colour - I can't read what is there !!

Pete

There doesn't appar to be any unique identifiers for the string portion.

It seems you are pulling the first letter after the period in the first line, "U XMAS LG SQUATTER" but are attempting to ignore the "A" in the second line.

Extraction is considerably easier if there's some sort of pattern or standardized format. Is there something special about each line or rules that need to be followed?

Do you need to always Ignore the first A after the fourth period? The more information and rules you can state upfront, the better.

Originally Posted by daffodil11

There doesn't appar to be any unique identifiers for the string portion.

It seems you are pulling the first letter after the period in the first line, "U XMAS LG SQUATTER" but are attempting to ignore the "A" in the second line.

Extraction is considerably easier if there's some sort of pattern or standardized format. Is there something special about each line or rules that need to be followed?

Do you need to always Ignore the first A after the fourth period? The more information and rules you can state upfront, the better.

Sorry for the confusion, I will ignore the U to make it consistent with other A below.

Originally Posted by XOR LX

Hi,

The only way to produce generic formulae to solve these types of problems is if there are logical, generalisable rules governing which parts of the strings are to be extracted in each case.

For example, you might say:
"The first substring to be extracted will always be that which occurs between the second and third decimal points."

Looking at your next set of desired extractions, I can't see a simple logic which fits all - could you please provide it, and, also, confirm the veracity of the above statement for your first desired extraction?

Regards

Edit: @daffodil I appear to have just echoed your sentiments entirely!

"The first substring to be extracted will always be that which occurs between the second and third decimal points."


You are right for the first substring. the second substring I could not find the logic behind that. If you know how to extract the first substring. it would be good enough for me. Thanks

I agree with the other two posters, but here's an attempt to set you on your way. With those strings in A1:A3, put this in B1:

=TRIM(MID(A1,FIND(".",A1,4)+1,FIND(".",A1,8)-8))

and copy down, and this will give you the number that you want (as a text value). Put this in C1:

=LEFT(TRIM(RIGHT(A1,LEN(A1)-FIND(".",A1,18))),18)

and copy down - this will give you approximately what you have asked for (actually 18 characters after the 4th full-stop, with leading spaces removed). You might have to do some manual adjustments if you want exactly what you highlighted.

Hope this helps.

Pete

Pete,

Thank you and it works great!

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

Also, you might like to pass on thanks directly to posters that have helped you (not just in this thread) - you can do that by clicking on the "star" icon in the bottom left corner of any post you have found to be helpful, and this will also add to the poster's reputation (the green bars in the profile).

Pete