Extract text that has a delimiter

kmham

New Member
Joined
Mar 6, 2008
Messages
40
Office Version
  1. 365
Platform
  1. Windows
I know this can be done using text to columns, however, my situation requires that I do this with a formula in a cell next to my text string (sorry, no VB code).

Constants:
1- There are always 6 values separated by 5 semi-colons
2- None of the "good" data will have semi-colons (the only time a semi-colon is found in the text string because it's my delimiter
3- The "good" data between the semi-colons can vary in length

Here are 2 samples of the data:
123.1.135.2;255.255.0.0;yahoo.com;ABC v1.2.3;16A15B14;Status=Good
222.10.246.30;255.255.0.0;cnn.com;DEF v2.30.40;17C2D52;Status=Failed

For my formula, I want to extract out the 4th value (which is "ABC v1.2.3" for record 1 and "DEF v2.30.40" for record 2.

Thanks in advance!
 

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.
Not sure if I follow this 100%:
2- None of the "good" data will have semi-colons (the only time a semi-colon is found in the text string because it's my delimiter

In your example the "good" data had a semi-colon attached at the end of it? Will the good data always be found starting at the 3rd semi-colon?
 
Upvote 0
<html><head><title>Excel Jeanie HTML</title></head><body>

<!-- ######### Start Created Html Code To Copy ########## -->

Excel Workbook
A
1
2
3
4
5
6123.1.135.2;255.255.0.0;yahoo.com;ABC v1.2.3;16A15B14;Status=Good
7ABC v1.2.3
8
9
Sheet3




<!-- ######### End Created Html Code To Copy ########## -->

</body></html>
 
Upvote 0
Why not just...

Excel Workbook
A
1123.1.135.2;255.255.0.0;yahoo.com;ABC v1.2.3;16A15B14;Status=Good
2ABC v1.2.3
Sheet1
 
Upvote 0
iliace - thanks! that's exactly what I needed. iggydarsa - looks like your formula would work find as well.

iliace - what does the "201" in (CHAR(201) stand for? Is this the system code for a semi-colon or something?
 
Upvote 0
iliace - one more quick question. Assume that my data stays the same. Do I use the same formula if I wanted to extract the 1st, 2nd, 3rd, 5th, or 6th value/field? I'm seeing how this works and I can get it to work for 2nd through 6th by changing the values 3 & 4 in the formula you provided, but I don't see how I could modify this formula to pull the 1st field. Sorry, this is just more for my understanding in case I have the need for it in the future. Thanks again!
 
Upvote 0
CHAR(201) is an extended character that's unlikely to be in your data. I picked that number arbitrarily.
 
Upvote 0
Hi

Assuming your text in A1 then you can use the following formula:

=TRIM(LEFT(SUBSTITUTE(REPLACE(A1,1,FIND("@",SUBSTITUTE(";"&A1,";","@",4))-1,""),";",REPT(" ",100),1),100))

Amend the red 4 to whatever position you want ie 1 will return 123.1.135.2 and 2 will return 255.255.0.0 in your first stated example etc. If the @ character may appear in your text you can replace that with another (or a multiple occurence which would make it more robust).
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,549
Messages
6,114,264
Members
448,558
Latest member
aivin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top