Text to Column based on Bold/Regular Text

[Mgc26133]

Hello all

I was recently asked to include the name of customers to a report I've been making. Problem is, the tool that is giving me this data also puts a bunch of other unwanted information in the same cell.

Eg: "Customer name License no Rezervation no Bunch of characters that appear to be random"

I would have used "Text to Columns" but the only thing that's separating these informations is bold formatting. And since the customers can have 3 names, 2 surnames and vary in length & character number, I haven't been able to think of a working method for all cases.

Is it possible to do this (via Text to Columns or any other method)?

PS: One thing that might help is all "Licence No" appear to include at least 1 numerical character (you can see in attached sample), but since the data is thousands of rows I haven't been able to confirm this, so I don't quite want to rely on it. Still, thought I'd mention it.

Best regards

[AliGW]

You cannot use formatting as an identifier in either Text to Columns OR a formula.

Best I can offer (but it will pull only the first two words):

=TRIM(LEFT(SUBSTITUTE(A2," ",REPT(" ",99)),198))

If you can establish a logic, then we might be able to improve on this, however with the alphanumeric codes as they are, I really can't see one.

[janmorris]

1. does the reservation number ALWAYS have a hyphen in it?

2. is the string between the name and the reservation number ALWAYS only 1 string of unknown length of random characters? or can it be more than 1 string?
for example, is it only string1010 or can it be string1010 string2ewe

[Mgc26133]

1- It does always have hyphen.
2- Just 1. Length can vary though, sometimes it's 3 characters, sometimes up to 15-20. But always only 1 string.

[AliGW]

Do you have a definitive list of customer names?

If you do, then you can use this:

=IFERROR(LOOKUP(1000,SEARCH(list_of_customers,A2),list_of_customers),"")

[janmorris]

in cell C2, try this formula, and drag down:

Please Login or Register  to view this content.

[AliGW]

That's a MONSTER, Jan!

[janmorris]

@AliGW, hahaha, yeaaa... sorry.... im not a master in excel, i pieced it together by first getting the data before and including the hyphen, then using that particle to extract before the last space, then doing it again....

heres the worksheet....


i have worked out how to do the second last space without nesting again, so here is the revised formula:

Please Login or Register  to view this content.

[Mgc26133]

"im not a master in excel"

Looks to me you are! I'm at a loss of words. Brilliant! Many thanks Sent rep

I will study your steps and see if I can understand your logic - and the functions you used: I'm not familiar with the FIND and SUBSTITUTE functions.. Guess you learn new things everyday

[AliGW]

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

Also, if you have not already done so, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of all those who offered help.

[Mgc26133]

Hi Ali

I didn't mark it solved for now in case I have further questions. But I will, don't worry

Thanks!

[davsth]

the below may be a fraction shorter
=LEFT($A2,FIND("-",$A2)) finds the string ending with the -


=FIND("^",SUBSTITUTE(A2," ","^",LEN(A2)-LEN(SUBSTITUTE(A2," ",""))-1)) finds the 2nd last space, we want this in the above formula
so substituting for a2
=FIND("^",SUBSTITUTE(LEFT($A2,FIND("-",$A2))," ","^",LEN(LEFT($A2,FIND("-",$A2)))-LEN(SUBSTITUTE(LEFT($A2,FIND("-",$A2))," ",""))-1))

we want the left of this string this many character -1
=LEFT(A2,FIND("^",SUBSTITUTE(LEFT($A2,FIND("-",$A2))," ","^",LEN(LEFT($A2,FIND("-",$A2)))-LEN(SUBSTITUTE(LEFT($A2,FIND("-",$A2))," ",""))-1))-1)

perhaps a fraction shorter

[Mgc26133]

Thanks everyone for all the help!