Hi,

Journal:JNL231648:::EX-WRC18:World Retail Congress 2018:Invoice SIN106952:1517,:::

How can I extract only SIN106952 please.

Thanks,

Please post a sample sheet with various examples of data and requested results

Or
Data - tex to columns - use : as delimiter - do not import columns you do not need

__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post

Specifically "SIN106952"? Or invoice number following string "Invoice" and continuing until first occurence of ":"?

When 2nd, then (on fly as I don't have Excel at moment - so maybe you have to make corrections)

Thank you for looking into this for me. However, the result gives: 2018. I attached example sheet herewith.

Attached Files

Extract Text string example.xlsx (9.8 KB, 9 views)

I had to log on to my work computer and check it out - there must be -7 instead of +7 twice in formula.

Another way

=MID(B3,FIND("SIN",B3),FIND(":",B3,FIND("SIN",B3)+ 1)-FIND("SIN",B3))

OP has ":" in original string BEFORE "Invoice" too! And I'm afraid OP needs all invoice numbers, not only ones starting with "SIN".

In case this is an one-time project, then the best solution is to use Peckoflyer's solution (Text to Columns) to get all same type strings into separate columns (it looks like number of ":"s in every string is same) - then it will be looking for right column to get invoice numbers.
If this will be repeated import procedure, then choices will be:
1. Have a separate empty sheet for import data (DataImport), and a separate sheet for main data (Data). Copy imported strings into DataImport. Use Text to Columns to distribute data into columns. Copy wanted columns of data into table on sheet Data (Add to bottom of table). Delete all columns with data in sheet DataImport. Downside: A lot of manual work every time OP imports new data;
2. Have a separate sheet for import data (DataImport) with column for imported strings, and a separate sheet for main data (Data). Copy imported strings into DataImport (add to column for imported strings). Use a lot of helper columns with formulas to get all parts of strings into separate columns (Find part of string left of 1st ":" and right of 1st ":". Repeat the same process with right part of string from previous step - until all parts are read into separate columns. In sheet Data, use formulas to read info from proper column in sheet DataImport. Downside: A lot of formulas are used, which may slow down the workbook considerably, when the number of table rows gets over 10000;
3.Import new data into text file (or rename the file you get with new data as text file - change file extension to "txt"). Open new Excel instance. Start Open File dialog, set file type to Text, and select import file. Open. Set delimiter to ":", optionally set formats for columns, and open file - data is read in into different columns. Copy data from this file into your workbook.

You may be right. Only OP knows.

Anyway I think you should keep your sarcasm for yourself. Your know-all attitude is a bit tiring.

Still this is about 20% shorter
=MID(B3,FIND("Invoice",B3)+7,FIND(":",B3,FIND("Inv oice",B3))-FIND("Invoice",B3)-7)