extract string of characters

[faughabealach]

this is probably easy for some people but I want to extract characters from a string starting after a specific character and ending at the last character. The size of the string of characters may vary eg in the following I want to extract from each line the string of characters after ": ", and display each one in a separate cell. I tried the RIGHT function but as the no of characters can vary I could not get it to work

Quote Number: 1234567891 V01
Sold To: anycompany PLC
End User: everycompany Ltd
Order Type: Sale
List Price: 124,548.68

so what I need to see is
1234567891 V01 anycompany PLC everycompany Ltd Sale 124,548.68
can anyone help

[pecoflyer]

Hi and welcome

it can be done with formulas, but I suggest using Data - Text to columns - Use : as delimiter and in the last part of the wizard select not to import the first column.
If you need to move to a row you can paste Special _transpose

The question is : Is this OK for your needs?

[ConneXionLost]

If you really need to use a formula (despite Pecoflyer's recommendation), then try:

Code: Select all

=IFERROR(TRIM(MID(A2,SEARCH(":",A2)+1,LEN(A2))),"")


Cheers,