Excel has a number of text functions that can help you to extract a word or a text from another text string. Which function, or combination of functions, to use depends on your situation.

Example:

Suppose that cell A1 has the text string:

“Creativity requires the courage to let go of certainties”

1. To get the first 5 characters of the string, we use the LEFT function:

=LEFT(A1,5)

The result is “Creat”

2. To get the last 11 characters of the string, we use the RIGHT function:

=RIGHT(A1,11)

The result is “certainties”

3. To get 7 characters from the string starting with position 10, we use the MID function:

=MID(A1,10,7)

The result is “y requi”.

4.To get the first word of the string, we use the LEFT and FIND functions:

=LEFT(A1,(FIND(” “,A1)-1))

The result is “Creativity”.

The FIND function here will give us the position of the first SPACE. We subtract 1 to get the length of the first word. That length is used as a parameter for the LEFT function.

Hi I need assistence with this situation.

In column “B” I have a series of quetions, e.g “WHAT IS THE REPORT NAME? WHAT IS THE REPORT PATH? WHAT IS THE PROMPT ANSWERS THE USER USED?”.

I need to extract every quetion to a different column, can this be done?

Column “C” should be “WHAT IS THE REPORT NAME?”

column “D” should be “WHAT IS THE REPORT PATH?”

column “E” should be “WHAT IS THE PROMPT ANSWERS THE USER USED?”

Hi,

Try this (Assuming we are in Row 1):

C1: =LEFT(B1;(FIND(“?”;B1)))

D1: =MID(B1;LEN(C1)+2;FIND(“?”;B1;LEN(C1)+2)-(LEN(C1)+1))

E1: ==RIGHT(B1;LEN(B1)-LEN(C1)-LEN(D1)-2)

Note that I use “;” as a separator for parameters. Some installations use “,”.

I have tested this in Excel 2007, but I guess the functions are the same across different Excel versions.

Regards.

Hi there

Supposed I have a string of text like this in A1:

CATHY 750 0 -120,000,000 JPY

May I know the formula to extract the figure “-120,000,000” from the above string?

Regards

Assuming that the string is in cell A1 and the length of the text to be extracted is fixed then you can try this formula:

=MID(A1;FIND(“-“;A1)+1;11)

The FIND function will locate the position of the “-“. We add 1 to get the start position of the required string (the number 12,000,000,000). The MID function will then extract the number.

Note: You may have to use “,” instead of “;” in the formula depending on your installation.

Hello,

I have a comments field that has 60+ characters. Somewhere in the cell, there are the letters “BL” or “NO” or “NO.” There is a string of letters and numbers after BL/ NO/ NO. and the length of this string varies, but they will always be followed by a space. Is there a way to do it?

Thanks,

I don’t think there is an easy way, however you can try this formula (assuming your string is in A12):

=MID(A12;FIND(“BL”;A12)+2;((FIND(” “;A12;11))-(FIND(“BL”;A12)+2)))

Here I am assuming that you know in advance that the letters are “BL”. If not then this may require another complex formula to decide what the letters are, and probably put them in a hidden column.

I want to do something similar. My field contains a combination of numbers, first name, last name:

123 Joe Smith

I’d like to take the number and put it in one column, and separate the first/last name into their respective columns….

Please help.

UPDATE:

I figured out how to extract the number:

=LEFT(G2,FIND(” “,G2)-1)

But when I try to do the names, it gets cut off to four characters ;-/

f you are using Excel 2007 or later try using the “text to columns” feature in the Data tab. See this post:

http://www.exceldigest.com/myblog/2011/03/20/how-to-split-contents-of-a-cell-into-adjacent-columns/

Hi there,

Supposed I have a text in a cell that is very long, but there is one sentence that I would like to keep and delete the remainder of the text. That sentence always start with the same 3 words and keep the whole sentence until the first period is met.

For ie, anythinggjljksdlkjfalskjfdaljf. fkljsdlfjslfkjsdalk For a complete list of patch download links, please refer to Microsoft Security Bulletin MS11-002 (http://www.microsoft.com/technet/security/bulletin/MS11-002.mspx).,

dljsdlkjfsldjflsdjf dsfljdsfljsdflj

sdflsdjffsd

So here just keep ” For a complete list of patch download links, please refer to Microsoft Security Bulletin MS11-002 (http://www.microsoft.com/technet/security/bulletin/MS11-002.mspx).” and delete the text before and after in that cell.

What would be the VBA code for it?

`Option Explicit`

Function ExtractText(myRange As Range)

Dim iStart As Variant, iEnd As Variant, ilength As Variant, iIgnore As Variant

ilength = Len(myRange)

iStart = WorksheetFunction.Find("For a complete list", myRange)

iEnd = WorksheetFunction.Find("mspx", myRange)

iIgnore = ilength - iEnd - 6

ilength = Len(myRange) - iStart

ExtractText = Mid(myRange, iStart, (ilength - iIgnore))

End Function

Create a user-defined function using the code above, then use it in your worksheet as follows (assuming that your text is in cell A3):

=ExtractText(A3)

From the string below, how can I pull the value of Joe Smith?

The user account CN=Joe Smith,OU=B11111,OU=city state,OU=XXXX Migrated Sites,OU=United States,DC=xxxxxxx,DC=xxx was disabled.

Assuming that your text is in cell A1, use this formula:

=MID(A1;FIND("CN=";A1)+3;FIND(",";A1)-FIND("CN=";A1)-3)

You may have to change “;” to “,” depending on your installation.

I’m trying to extract “ENG-085” into one row (c2), and EQV-ENG085 in to the next row (b3) (and also copy/paste psy-140 ). Then I’d like the next part of the string (triggered by ‘take’ ) to extract ENG-090 and paste into the next column (column C). So, ENG-090 would be in column C2. Any ideas on how to get this workin.g.?’

row 1 A B

row 2 PSY-140 # Take ENG-085 ENG-110 or EQV-ENG085; # Take ENG-090 EQV-ENG090 or ENG-131;

psy-140 is in the first column (a),

#take…etc. is in the next column (b)

Here is a general rule to apply with any text:

To extract text you use the MID function: MID(mytext;start position; the length of text to extract).

mytext is known : B2

Length is known : 7

What is missing is the start position. To get this we use the FIND function:

FIND(“ENG-085”;B2)

Finally , we substitute the length of the string with the FIND function as follows:

=MID(B2;FIND(“ENG-085”;B2);7)

You can then apply the same rule to other strings.

Hi,

I have a text string something like this –

• Issue start date: 09-06-2011 • When does the customer get the error? : anytime • Which Handset is the customer using?: Samsung Galaxy Tab • Had customer recently begun using a different handset when the issue started?: No, always used the same device • Where is the issue: Indoors and Outdoors both • Affected postcode: AB1 2CD • Post code of area where service works fine: PQ1 2XY

What I want to do is extract the text “AB1 2CD” i.e. after text string ‘Affected postcode:’ in the next cell….

How can we do this?

Assuming your text is in cell A2, use this formula:

=MID(A2;FIND(“Affected postcode:”;A2)+19;7)

Please see the reply to Angel Fonseca above to understand the logic behind this formula.

Hi,

Suppose I have a column like:

A1= Metadatascripts

A2= SACDATA

A3= Documentation

A4= SQLDEMSYSAC

How can I get the answer ‘Yes’ or ‘No’ in the cell B1 to the question: does the string “SAC” occures in column A?

In the example the answer has to be ‘Yes’, because we’ve got the string SAC in cell A2 and A4.

Use this formula in B1:

`=IF(COUNTIF(A1:A4;"*SAC*")>0;"YES";"NO")`

1. The inner function

COUNTIF(A1:A4;”*SAC*”)will count all occurrences of the letters “SAC”. The “*” means look for the letters “SAC” anywhere within the cell.2. The outer function

IFwill evaluate the result ofCOUNTIF, and if the result is > 0 it will put “YES” in cell B1, otherwise “NO”.Thank you 😉

Hi,

Assuming I have in row 1 : Names (Code)

The names are of different length, but the code is 3 letters. Though sometimes there are 3 letters preceding the name (Company linked)

Examples of names : CNN Newyork (NYK), CNN Colombo (COO), Alantis (ALA), Hogwarts(HOG). (Different formating too)

How do I split the names into different rows?

IE :

Row 2 : CNN Newyork, CNN Colombo, Alantis, Hogwarts

Row 3 : NYK, COO, ALA, HOG

Thanks!

Suppose your text is in cell A1, and you want to extract the code to B1.

Use this formula in B1:

`=MID(A1;FIND("(";A1)+1;3)`

Autofill for the rest of the rows.

The

FINDwill give us the position of the “(” which indicates the start of the code. We add 1 to get the position of the code itself; We feed this into theMIDfunction to get the code.Hi Guys,

Sorry to jump in but i have a question

i have – My Name is John (Mr) in one cell, Alias Magie (Mrs) and i wanted to know how i can get only the MR/MRS extracted???

Please help

Thanks for your reply

Telconap

This formula will look for either of the words “Mr” or “Mrs” in cell A1 and then it puts the value in the cell where the formula is.

=IF(MID(A1;FIND(“Mr”;A1);3)=”Mrs”;”Mrs”;”Mr”)

I prefer that you type the formula through the keyboard because the quotes sometimes get messed up with copy / paste.

Also you may have to use “,” instead of “;”.

Hi Admin

Dude you are a great man

Thanks for the formula, i had only to use (,) instead of(;)

help pls again

i have some name in cell a1,a2,a3:

Alsson Magie,

John river Ferdo,

Jr-fred Cossi

How can i seperate the name like: cell a4 Alsson,John,Jr-Fred and in cell a5 Magie, Ferdo Cossi..

Appreciate your help dude,

1. For the left side use this formula:

`=MID(A1;1;FIND(" ";A1)-1)`

2.For the right side use this formula:

`=RIGHT(A1;LEN(A1)-FIND(" ";A1))`

The second one is tricky though because if the full name consists of three names then the right part could have 2 names.

How can you combine text manipulation to extract the middle numbers of the following example and then copy it down to extract the middle numbers only of the rest of the data in the column:

dyak-23+my (extract the 23 only)

Then copy down to extract the following:

ary-412+zzxt (extract only the 412)

pg-9075+xrw (extract only the 9075)

Assuming the text is in cell A1 then use this formula:

`=MID(A1;FIND("-";A1)+1;FIND("+";A1)-FIND("-";A1)-1)`

You can then use autofill for the rest of the cells.

Hi there,

I need your help to extract a couple of lines from a cell. For the example (shown below) I need to extract ticket number and IP Addresses listed to a new worksheet in such a way that the worksheet captures the IP addresses for the given ticket number

something like…

Ticket Number IP Address

XXXXXXXXXXX XX.XX.XX.XX

YY.YY.YY.YY

ZZ.ZZ.ZZ.ZZ

Example:

“The following report has been generated and IP address have been listed.

Ticket number: XXXXXXXXX

IP Address: XX.XX.XX.XX

YY.YY.YY.YY

ZZ.ZZ.ZZ.ZZ”

How can achieve this with formula?

Assuming that the text is in cell A1 and the length of the Ticket number is 9 digits and the IP Address is 11, use this formula for Ticket number:

=MID(A1;FIND(“Ticket number:”;A1)+14;9)

And this one for IP Address:

=MID(A1;FIND(“IP Address:”;A1)+12;11)

Adjust field length if necessary.

I have these data in one cell:

Jefferson Lane 25

Dillinger 88

Park Avenue 22

Winchester Street 45

I would like to parse the sell and extract the street number and place it in a separate cell. The Text To Columns feature won’t do the trick because of the spaces contained in the names of some of the streets.

Any ideas ?

Because there is no common criteria in the street name/number to use in extracting it, The only way I can think of is to have a delimiter before and after the street like a “,” or “-“.

I’d really like to know a formula to look for specific text within other text, and if found display that text. The text items in the cell are always separated by commas, but there other irrelevant text items in the same cell.

Example:

column B to look to see whether column A contains “red”, or “Bill and Ben”, or “big” and display all of these words found.

A1: red,green,blue,black,Bill and Ben,Flower Pot Men,big,small

B1: red,Bill and Ben,big

A2: red,white,green,blue,orange,black,Flower Pot Men,big,small

B2: red,small

A3: white,green,blue,purple,black,Flower Pot Men,big,medium,short

B3: big

It is important that column B maintains the separation of words by commas.

If it’s not possible to pull out the words I need, perhaps it’s possible to exclude/delete the other unnecessary words?

Many thanks for any help!

Thanks anyway but I found an answer to this if anyone’s interested…

http://www.mrexcel.com/forum/showthread.php?t=581545