Microsoft Office Forums

Go Back   Microsoft Office Forums > Microsoft Excel > Excel

Reply
 
LinkBack Thread Tools Display Modes
  #1  
Old 01-12-2019, 08:18 AM
Haha88 Haha88 is offline Windows 8 Office 2010 32bit
Advanced Beginner
 
Join Date: Mar 2015
Posts: 40
Haha88 is on a distinguished road
Default Help with Formula to extracting text from text string

Hi,



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

How can I extract only SIN106952 please.

Thanks,
Reply With Quote
  #2  
Old 01-12-2019, 08:38 AM
Pecoflyer's Avatar
Pecoflyer Pecoflyer is offline Windows 7 64bit Office 2010 64bit
Moderator
 
Join Date: Nov 2011
Location: Brussels Belgium
Posts: 2,258
Pecoflyer is a glorious beacon of lightPecoflyer is a glorious beacon of lightPecoflyer is a glorious beacon of lightPecoflyer is a glorious beacon of lightPecoflyer is a glorious beacon of light
Default

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
__________________
Problem solved ? Let others know by clicking " Thread Tools" then " Mark thread as solved".( This can be undone if need be)
Want to thank for the help received ? Click the scales symbol in the upper right corner of a post from the person you want to thank.
Reply With Quote
  #3  
Old 01-12-2019, 08:48 AM
ArviLaanemets ArviLaanemets is offline Windows 8 Office 2016
Expert
 
Join Date: May 2017
Posts: 352
ArviLaanemets is on a distinguished road
Default

Quote:
Originally Posted by Haha88 View Post
How can I extract only SIN106952 please.
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)
Code:
=TRIM(LEFT(RIGHT(YourString,LEN(YourString)-FIND("Invoice",YourString)+7),FIND(":",RIGHT(YourString,LEN(YourString)-FIND("Invoice",YourString)+7))-1))
Reply With Quote
  #4  
Old 01-12-2019, 09:00 AM
Haha88 Haha88 is offline Windows 8 Office 2010 32bit
Advanced Beginner
 
Join Date: Mar 2015
Posts: 40
Haha88 is on a distinguished road
Default

Quote:
Originally Posted by ArviLaanemets View 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)
Code:
=TRIM(LEFT(RIGHT(YourString,LEN(YourString)-FIND("Invoice",YourString)+7),FIND(":",RIGHT(YourString,LEN(YourString)-FIND("Invoice",YourString)+7))-1))
Thank you for looking into this for me. However, the result gives: 2018. I attached example sheet herewith.
Attached Files
File Type: xlsx Extract Text string example.xlsx (9.8 KB, 2 views)
Reply With Quote
  #5  
Old 01-12-2019, 11:23 AM
ArviLaanemets ArviLaanemets is offline Windows 8 Office 2016
Expert
 
Join Date: May 2017
Posts: 352
ArviLaanemets is on a distinguished road
Default

I had to log on to my work computer and check it out - there must be -7 instead of +7 twice in formula.
Reply With Quote
  #6  
Old 01-12-2019, 11:52 AM
xor xor is offline Windows 10 Office 2016
Expert
 
Join Date: Oct 2015
Posts: 943
xor is a jewel in the roughxor is a jewel in the roughxor is a jewel in the roughxor is a jewel in the rough
Default

Another way

=MID(B3,FIND("SIN",B3),FIND(":",B3,FIND("SIN",B3)+ 1)-FIND("SIN",B3))
Reply With Quote
  #7  
Old 01-13-2019, 12:35 AM
ArviLaanemets ArviLaanemets is offline Windows 8 Office 2016
Expert
 
Join Date: May 2017
Posts: 352
ArviLaanemets is on a distinguished road
Default

Quote:
Another way

...Journal:JNL231648:::EX-WRC18:World Retail Congress 2018:Invoice SIN106952:1517,:::
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.
Reply With Quote
  #8  
Old 01-13-2019, 01:33 AM
xor xor is offline Windows 10 Office 2016
Expert
 
Join Date: Oct 2015
Posts: 943
xor is a jewel in the roughxor is a jewel in the roughxor is a jewel in the roughxor is a jewel in the rough
Default

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)
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Help with Formula to extracting text from text string Haha88 Excel 9 02-05-2018 01:04 AM
Formula to Extract text from a text string Haha88 Excel 2 11-14-2017 01:32 AM
Extract text from a text string Haha88 Excel 8 02-13-2017 05:06 PM
Way to search for a string in text file, pull out everything until another string? omahadivision Excel Programming 12 11-23-2013 12:10 PM
Extracting a phone number from a string that contains text and numbers. hommi16 Excel 2 06-05-2013 09:19 PM


All times are GMT -7. The time now is 09:08 PM.


Powered by vBulletin® Version 3.8.1
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
SEO by vBSEO ©2011, Crawlability, Inc.
MSOfficeForums.com is not affiliated with Microsoft