Move data from one column to another based on the first character

[ifrydr]

Hello -

I am posting this again because I really need it to work, and someone else pinged me and asked if I ever figured it out.

I have data that is exported that I need to manipulate. It is a "Where Used" report for Bills of Materials etc. The problem is that when it is exported it puts the "part number" as well as the "top level assembly number" in column A. The part number is listed first and then the assemby(s) will be listed in the next row(s) denoted by a space in front of the Assy number. I want to move the indented data i.e. assy number to column B.

Is there a way easy/automated way to do this?

Column A looks like:

TEK6000015
-indent/space here-TEK8000034
-indent/space here-TEK8000209

Ideally I would like column A to have "TEK6000015" and column B to have "TEK8000034 and TEK8000209" which are the indented values.

Make sense?

p.s. The entire sheet has ~29k lines

Thanks!!

 

[texasalynn]

So does the "indented" data have multiple spaces??? How is it exactly? You might be able to do a text-to-columns on it and mark the "treat consecutive delimiters as one"

 

[ifrydr]

It has one space by default when I run the report. But to make it look a bit better visually I do a find and replace the space with ~15 spaces... If that makes sense.

Holy Moly, that was easy!! It worked great, Thank you!!

 

[texasalynn]

so instead of doing the replace with 15 spaces, do the text-to-columns with space as the delimiter

 

[ifrydr]

Yep. Exactly what I just did... Never thought it would be so easy!

Thank you very much!!

 

[texasalynn]

Ok good - you made my day! Was beginning to think my answers were not working