Extract Text from Cells in Excel

I'm compiling a cheat sheet of formulas for my office workers to use. When I get to the Text functions, I can find examples of how to do just about everything except extract text before/after a particular string. I've found a few formulas for doing it after a symbol, but they don't work when it comes to a string.

For example, say I have 15 paragraphs that all begin with "Yo mamma's so ugly that..." and I want to extract whatever follows after "that". Or, say I have 1000 lines of text formatted Code:

j0125 abcd  1 HERD alpha 87 dog

and I want to extract everything after the 4th character string (HERD in this example), or before the 4th string. Is there a way to do that in a formula that can adapted to any situation?

Your help in augmenting my knowledge is already greatly appreciated.

Thanks,
John

I am working with an Excel spreadsheet issued by the SBA (Small Business Administration) where they list performance of loans based on specific NAICS Codes. Unfortunately they list the NAICS Code number and the Name of that NAICS Occupation in the same column.

Example: it will read :

311615--Poultry Processing 812210--Funeral Homes and Funeral Services 421730--Warm Air Heating and Air-Conditioning Equipment and Suppplies Wholesalers 311222--Soybean Processing 325314--Fertilizer (Mixing Only) Manufacturing

The numbers are essentially random; I want to remove the numbers from the column but leave the text (that is the occupation names) so I can alphabetize the list. I don't need to retain the numbers (I have saved them in a separate worksheet) I just want to replace them with 'nothing', that is, delete them entirely. I have tried any number of 'find' and 'replace' fomulas to no avail. I don't want to go in and erase the numbers from 2600 rows, Help!!

Hi,

I have list of addresses that have address and suburb combined into one cell. The suburbs are all uppercase and I'm trying to find a formula to extract these to another cell.

Examples (located in column B of my spreadsheet)
Cnr Hollywood Drive and Wharf Road LANSVALE
Cnr Hamilton Drive & Tongarra Road ALBION PARK
2A Hillcrest Avenue BARDWELL VALLEY

I can use the following array formula found in another forum to extract suburbs with a single name but can't get the suburbs that consist of two names

=RIGHT(B2,MATCH(" ",MID(B2,LEN(B2)-ROW(INDIRECT("1:"&LEN(B2))),1),0))

any assistance appreciated

Hi all,

I'm trying to extract all the text in a cell which is on the right side of a comma (,)

Currently I am using this formula,

=RIGHT(C1,FIND(",",C1)-1)

However it sometimes is missing some of the data after the , and sometimes pulling a few characters before the ,

any suggestions on how to correct my formula please?

Thanks in advance.

Hi,

Can someone help me?

In column A I have some thousands of cells with lots of html text.
In each cell along the text there's a flash embed code which I need to extract and place in the next column. The code start with "object" and ends with "/object". How do I extract only this code and paste in column B?

THANKS for the help...

Knokenet

I have a sheet with over 20,000 rows of item #'s in Column A of a sheet.

I am looking to:

1) Find all items that have 2 dashes (i.e. "-"), then...

2) For those that have 2 dashes, in a new column, put the contents of the item # that come after the second dash.

Sample data is below:

abc-scott-special
kjh-jkhsakjjkhkjh-SAM
CB-123456jkh-bob
EX-1824ABCXYZ-justoneday
AB-123455
AB-765432
AB-NOSECONDDASH
AB-TWODASHES-testhere


You'll see some of the above has 2 dashes, and some only have one.

Any thoughts on how to do this?

Thanks a million!

OK, I don't know why I am having trouble with this.... I know what I want excel to do and what I was thinking of to get it to work. But I am having trouble getting it to do so. Currently I have =MID(N8,FIND(",",N8)+2,(LEN(N8)-FIND(",",N8))) which is simple enough.

My problem is extracting only up until the space after the comma. I was trying to use MID/FIND with the result of the above formula which creates a rather large formula, but it keeps throwing a #VALUE at me and I can't figure out the best way to do this.

Hello,

I can't figure out how to search for way to extract data from a text string and move the extract data to a new column. For the example jointed with this post I use a delimiter ":" to identify the text to extract.

Thanks for any helps.

Hello,

I have a text string that contains a social secuirty number and I want to extract it to a different column. Example:

Text string: D4AX3TY05T123456789T
I am trying to extract the SSN (underlined) and place it in a separate column. One thing about this text string is that the SSN starts at the 11th character and they all have a T at the end. Any help is appreciated.

fewaldron

I need to extract text after space in string e.g.

INV1019469 Intrum Justitia Oy
INV1028 Petskiboat Oy
INV102812 Palo, Tolvanen & Al
INV103 Fast Capital Oy

Result:
Intrum Justitia Oy
Petskiboat Oy
Palo, Tolvanen & Al
Fast Capital Oy

Thanks
Lasse

Thanks for taking the time to respond to this post. I appreciate your help.

The Problem: See attached file. I have data of variable row length presented in two columns. The data (in column b) appears as "4, Database 3, Database 4, Database 9, Database 10". I need to extract the first numeric text phrase (numbers from 1-10) into a separate column (column c) and extract the remaining text into another column (column d). The remaining text can be of variable length, but is always presented as alphanumeric text with commas if there is more than one.

Thank you!

Hi,

I have a column of cells with text like this:
Pour-Over Coffee Urn, electric, single 3 gallon cap., brews in 1 lb. batches, side mounted control panel, s/s construction

I am trying to copy all text before the second or third instance of the comma into an adjacent cell.

I've been using: =LEFT(C548,(FIND(",",C548,1)-1))
But that gives me only the text before the first comma. How do I change which instance of the comma in my formula?

Thank you!!!!

I have a column of times, that are actually dates of 1/0/1900 plus time, from which I want to extract the AM or PM status. I can format it to show this, but it's really just a fraction of a day. I need the actual text "AM" or "PM", not a decimal value.

Is there a formula to do this?

Good Afternoon, I am currently working on a project and need some help data mining a extract from a database that's in excel.

Some of the data I was given has latitudes and longitudes which are valuable to me but some of the information was entered incorrectly and the lat and long columns have only zeroes for some entries. I used some VBA to sort out all the ones that have zeroes in the lat and long columns because I noticed in a comments block some of them have the latitude and longitude placed in the comments instead of the designated spot.

An example of one of the comment cells is this:
Search and Rescue 30NM S MARTHA'S VINEYARD 40-37.4N 070-43.3W MEDEVAC OF 35YOM E

As such the problem I am having is figuring out how to search through the cells for ones that have a lat and long in them since there is other information and numbers in the comment cell. Keep the cells that have latitudes and longitudes and get rid of the rest. The red highlighted information is what is valuable to me. Another problem is not all of the latitudes are in that format sadly as can be seen by this example cell:

SAR Unreported Vessel/ 42 01. N 067 25. W CLOSED AREA II/041527ZAUG06

If anyone has any insight/references into how I could go about doing this I would pretty much owe you my life since doing it manually would take ages for myself and my group on this project.

Thanks!

I have a column of cells over 2000 rows that have a bunch of text in each cell. In the middle of this text in each cell are websites (not hyperlinked). I was wondering if someone wrote a macro or formula to extract URLs located in a bunch of text? I can parse this out but doing some delimiting but that will take longer.

Hi, I've tried using the search function to find the answer on my own, but I wasn't able to solve my problem.

If someone is able to help me out I'd really appreciate it. I don't know VB so an excel formula is what I really need.

Here is what I want to do. I need to extract

Here is an example:
3M EMI Copper Foil Shielding Tape 1181, 2 in x 18 yd, (50,80 mm x 1,5 m), 5 per case

I need to get the string before the first comma "3M(TM) EMI Copper Foil Shielding Tape 1181".

And also the data after the first comma and before the last comma. Often I can have 4-5 (or more) commas. So I'd need to get " 2 in x 18 yd, (50,80 mm x 1,5 m)" out of that.

Thanks so much!

Hi,

I have a string of information in a field and I want to extract the last word into another cell.
The values in the field are separated by spaces, so to extract the first bit of info I am using FIND to locate the first space then using LEFT with the result of the FIND-1. So this is simple.

I'm not finding it so simple to do this the other way round to pull out the right hand side of the text to do the equivalent for the last word.

I'm sure there's an easy way to do this, but as yet I haven't found it.

Any ideas anyone?

Thanks in advance.

Hello,

I have a column of text that has some words that are formatted in "Bold".

Douglas DC-9 aircraft
Hughes aircraft

I was hoping someone here could help me write a vba macro to loop through all the rows and pull out only words in Bold and place them in the adjacent column.

Thanks in advance.

Hi all,

I'm trying to find/create a function to extract the nth character in a string of characters.

For instance, in A1 I have the text:
"wheninthecourseofhumaneventsitbecomesnecessaryforonepeopletodissolvethepoliticalbandswhichhaveconne ctedthemwithanotherandtoassumeamongthepowersoftheearththeseparateandequalstationtowhichthelawsofnatu reandofnaturesgodsentitlethemadecentrespecttotheopinionsofmankindrequiresthattheyshoulddeclarethecau seswhichimpelthemtotheseparation"

I'm trying to extract the characters 33, 36, 38, 25, etc.

Any idea ?

Please note that I can't use VB so it would have to be an excel formula (if possible) !

I have a paragraph in a spreadsheet, like so:

-----------------------
1,000 Vegetarian Recipes by: Gelles, Carol DESCRIPTION: ISBN:
0-02-542965-5 Title: 1,000 Vegetarian Recipes Author: Gelles, Carol
Category: Cooking, Food Wine : Baking : General Publisher: John Wiley
Sons Inc Date: May 1996 Format: Hardcover Condition: Used; Very Good
Seller's Notes: Small smudge on the inside fold of the dustcover.
Record Number: 328 ABOUT THE BOOK: Book Description Not just for
vegetarians, there's never been a better time for this award-winning
book! Carol Gelles, one of t
-----------------------

Is there any way to extract the ISBN ('0-02-542965-5' in the example
above) and the Author ('Gelles, Carol' in the example above) and the
Format ('Hardcover' in the example above)??

These columns are extracted from a data-source that cannot be modified.
All of the paragraphs that will be extracted are not in exactly the
same format -- some of them say "editor" instead of "author" and, of
course, the information in the paragraph is all different lengths.

Thanks.

Barb

Hi,

I am trying to extract postcodes from a text string, unfortunately the postcode doesn't always appear at the end of the text string.

I'm not sure if this can be done with formulas or whether it requires VBA to accomplish the task.

My aim is to extract the postcode which I would like to appear in column F.

I have attached an example spreadsheet.

Thanks in advance for any help.

Ade

hi,

I have many registers with alphanumeric strings.
I want to extract only the text from these cells.

I have:
A2:FL9O7WER
A3:8T0AB765LE
A4:9FL7O8O4R4

and I need:
B2: FLOWER
B3: TABLE
B4: FLOOR

How to do this?. With a formula, udf or code?

Please help. Appreciate in advance your cooperation.
thanks,

XP
Excel 2003

I have cells showing text data like this:
BD/JOHNSON&COELHA/CF#WQ8J5D
CXLD/DUTHCER/523342

I want to extract the varying length characters strings from left to right before the first forward slash only.

Thanks for the help

Dear Experts,

I have unsuccesfully tried for a while to come out with a code that enables me to open files in any given line other than have always to start from the first one and go down until finding a tag.

I need that piece of code because I'm dealing with 20MB files with thousands of lines. I have a code that does what I want but it takes ages when the data I want to retrieve is close to the end of the file.

I had a look and haven't find any code that allows me to pass onto a variable data from a range of lines in a text file.

I'm attaching a text file example to show the structure of my data. Every block of data comprises three subblocks and blocks are separated by a blank line. In order to being able to identify blocks and subblocks I'm,at present, tagging with "class(j),tag(i)" the beginning and end of everysubblock.

May code populates 3 variables (one for each subblock) for any block (tag) defined by the user.But as I said it takes ages when it has to look for the tag 2000 in 50 files.

any help would be much appreciate it

Please let me know if clarification on my data is needed
Many thanks,
vbaer

Respected sir,

I ran with my legs - extract "with"
I have big cat - extract "big"

both words are extracted after two spaces

i want to have my house - Extract "have"
i want to dance in my house - Extract "dance"

Above both words are extract after three spaces

how do i do above

regards

juzar para

I have a textbox on one sheet that has multiple paragraphs - blocks of text separated by carriage returns.

I want some vba code that would, on another sheet, extract the contents of the textbox and put each paragraph in a separate cell.

For example: Sheet(1) has TextBox1. In this textbox is the following block of text:

[StartText]
This is paragraph one with some text in it.

This is paragraph two with additional text in it.

And this is paragraph three.
[/StartText]

In Sheet(2), I would have the following after running the script:

Cell B1 = "This is paragraph one with some text in it."
Cell B2 = "This is paragraph two with additional text in it."
Cell B3 = "And this is paragraph three."

All of this would be done in a loop so that the number of actual paragraphs in the textbox is dynamic and I could control the destination cells.

I've seen some forum entries that show how to extract the entire block of text out of a textbox into a single cell, but not one cell per paragraph (or carriage return).

Thanks in advance.

Troy

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!

Hi

I am trying to extract text from a cell, everything left of the second backslash (eg. Refining\Breakdown\ChipSupply\ChipSilo\Screw will become Refining\Breakdown)

I have tried using =LEFT(B33,FIND("\",B33)) but this only gets me past the first backslash

How do I get to the next one in the string?

Cheers

David

I have in this amazing forum found a formula to extract a textstring before a special character.

In this case I want the text before the "." dot

=LEFT(A1,FIND(".",A1,1)-1)

This works perfectly, but not for an E-mailadress.

Is there anyone that can find a formula that extract the text before the first dot in an E-mail adress

Thank's in advance