Free Microsoft Excel 2013 Quick Reference

Extract Text From Alphanumeric String

Hi,
I need to extract just the text from cells which contain both letters and numbers. There can be one or two letters, followed by one, two or three numbers. (For info, these are chemical elements and their masses).

So for instance,a cell could contain anything from the following B9, B11, S32, Ca44, Mo100, I129, Th226, U238 etc etc.

I know I've seen this done before, but I can't find it now.

It also has to be formula based, not using VBA.

Anyone remember how to do this please?

Thanks

Dave


Post your answer or comment

comments powered by Disqus
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,

Hi Guys

There are meny posts regarding how to extract the numbers from alpahnumeric text but none for the opposite; which is extract text from an alphanumeric cell. Or maybe I am not doing my searchs that well.

http://www.ozgrid.com/VBA/ExtractNum.htm
http://www.ozgrid.com/forum/showthread.php?t=73053
Extract Numbers And Text From Listbox Separated By Delimiter

Can someone please help me to extract the text part from cells that have values like,

Goodwood -2
Tabel Bay -2
Chekered -3

I only want the Goodwood, Tabel Bay part of the cell.

Regards

Hello All,

I need some help devising a formula to extract text from a string.

If we take the following examples:

Example 1:

Hello, my name is Matty__from England

Example 2:

__Hello, my name is Matty__from England

Note that in example 1, there is a double space after Matty, whilst in example 2, there is a double space at the start and after Matty.

In both examples, I just want to extract "Hello, my name is Matty", which means "from England" is ignored in both examples, and, in example 2, the surplus spaces at the side are trimmed off.

Hope this makes sense, and any ideas most welcome!

Thanks,

Matty

Hi,

i have a list of alphanumeric data in Column A1:A3 and require only the text from these strings to be posted in B1:B3

below is the data.

1880000SR 1188AED 1111111USD

the result should be

1880000 SR 1188 AED 1111111 USD

How can I extract the numbers from an alphanumeric string? The string is in a list list the one below. I need to extract the numbers, then format the column to read the numbers, include the comma and list as footage. Thanks in advance for all the help.
N/A N/A N/A N/A N/A 7,392FT 1,056FT 26,928FT 1,795.2FT N/A 12,672FT 264FT 27,984FT N/A 16,104FT 14,784FT 17,773.98FT 264FT 739.2FT 528FT 1,795.2FT 1584FT 792FT N/A 422.4FT 4,276.8FT 9,028.8FT N/A 13,200FT

Hi can one help in how to extract the text from particular string and paste the string into excel .
the sentence is identified by ;
so once ; is delimited it has to read the text till ; and has to paste in to excel.
it has to accpet all the spaces,other characters and has to paste in excel.
The striing looks like this

	VB:
	
Delete 
From	iw_patstrat_genpact_workdb.TCIND_WORKfebA; 
Delete 
From	iw_patstrat_genpact_workdb.TCIND_WORKfeb2A; 
 
INSERT	INTO iw_patstrat_genpact_workdb.TCIND_WORKfebA 
SELECT	FILL_PHCY_NBR, INVOICE_NBR, REFILL_NBR, RX_PHCY_NBR, RX_NBR, PROTOCOL_REASON_CDE, MESSAGE_1_TXT, 
CUSTOMER_NBR, CAST(PROTOCOL_TMS As Date), 1 
FROM	 eods_proddb_v.protocol_event 
WHERE	PROTOCOL_REASON_CDE = 'TCIND'
AND	CAST(PROTOCOL_TMS As Date) >= '2005-12-01';

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

i want ouput in this way
OutPut:

	VB:
	
Delete  From	iw_patstrat_genpact_workdb.TCIND_WORKfebA; 
Delete  From	iw_patstrat_genpact_workdb.TCIND_WORKfeb2A; 
INSERT	INTO iw_patstrat_genpact_workdb.TCIND_WORKfebA SELECT FILL_PHCY_NBR, INVOICE_NBR, REFILL_NBR, RX_PHCY_NBR, RX_NBR,
PROTOCOL_REASON_CDE, MESSAGE_1_TXT, CUSTOMER_NBR, CAST(PROTOCOL_TMS As Date), 1 FROM eods_proddb_v.protocol_event WHERE
PROTOCOL_REASON_CDE = 'TCIND' AND CAST(PROTOCOL_TMS AS DATE) >= '2005-12-01';

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


im tryinng the follwoing code.
but its not working for me


	VB:
	
 
Dim k As Integer 
Dim position As Integer 
Sub ImportData() 
     
    k = 1 
    i = 1 
     
    Filename = "C:Documents and Settingsp54g30DesktopTRC_SQL1.0.txt" 
     
     
    Open Filename For Input As #1 
     
    Do While (Not EOF(1)) 
         ' In this case the file is delimited by ; and contains several lines
         ' Read the file one line at the time
        Input #1, Streng 
         
         'Input streng into and array
        StrArray = Split(Streng, ";") 
         
         
        Call WriteToExcel(StrArray) 
         
        k = k + 1 
         
    Loop 
     
    Close #1 
     
End Sub 
 
Sub WriteToExcel(StrArray) 
     
    For j = LBound(StrArray) To UBound(StrArray) 
         
        Cells(k, 1).Value = StrArray 
         
    Next j 
     
     
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
it was pasting in this way.

	VB:
	
"Delete  From	iw_patstrat_genpact_workdb.TCIND_WORKfebA" 
"Delete  From	iw_patstrat_genpact_workdb.TCIND_WORKfeb2A" 
"INSERT	INTO iw_patstrat_genpact_workdb.TCIND_WORKfebA SELECT FILL_PHCY_NBR" 
INVOICE_NBR 
REFILL_NBR 
RX_PHCY_NBR 
RX_NBR 
PROTOCOL_REASON_CDE 
MESSAGE_1_TXT 
CUSTOMER_NBR 
CAST(PROTOCOL_TMS As Date) 
1 
FROM eods_proddb_v.protocol_event WHERE PROTOCOL_REASON_CDE = 'TCIND' AND CAST(PROTOCOL_TMS AS DATE) >= '2005-12-01'

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
the first two sentences are pasting in two rows correctly.
but the third statement is pasting form row 3 till row 8.which i dont want.
i want to paste the third statement in row 3.

thanks in advance.
krishna

Hi all

I need to extract the text from a string that is always less the last three digits. For example if A1 is abcdefg I always have to take off "efg" and extract the rest of it to the left but the length of the string can vary.

I can be dirty and record a macro where I find the length, subtract 3 and then using the LEFT worksheet function can get my result, but when I come to try to convert that to VBA I get stuck. If anybody could help I would be very grateful. What is the VBA equivalent of LEFT worksheet function and how would I go about using LEN in conjuntion with subtracting 3 from it?

Any suggestions greatly received
Reggie

Hi.

I am trying to extract text from a text string that corresponds to text in another column. For example, in the spreadsheet attached, I am specifying which database to reference in cell E6. Next, I am using cell D6 to specify which "Split" to use from the database. I need to be able to change these 2 parameters and have cell Q6 display the correct "Phase". The text string in the first column is some combination of "Split (cell D6) Phase (#1-16) Coor Phs. I want to display the "Phase" that corresponds with the text "ON" in the second column. I hope this makes sense.

Thanks in advance for your help.forum version.xlsx

How do I extract decimal numbers from alphanumeric strings to perform
calculations on the number, eg 25.1 km or 100 m.

Hi,

I'm a postgraduate student at Durham University in the United Kingdom and my research is on media coverage and humanitarian assistance of Natural Hazards and is heavily Excel-based but myself and my supervisors seem to have come to a brick wall and no one in my whole department know what to do.

We are looking for a function or formula that can return a number from an alphanumeric string that relates to a keyword in that string.

I shall explain...

We have one column that looks like:

7 Vietnamese women die in quarry collapse--officials - Inquirer.net
1 dead, 1 missing as avalanches hit stormy California - San Diego Union Tribune
1 person dead, 1 missing skier found in SoCal avalanche - San Jose Mercury News
Landslide kills 7 on Indonesia's Java - Reuters UK

And we need a column that shows only the number which relates to fatalities mentioned in each title.

I have managed to find a way of extracting the numbers from the alphanumeric strings but we have problems with titles like this:

Mingo National Wildlife Refuge Going Through "100 Year Flood" - KFVS

So we are looking for a way to extract numbers from the strings that contain keywords like "dead" "deaths" "fatalities" etc.

We are also looking for a way to extract the last part of the above strings:

- KFVS
- Reuters UK
- San Jose Mercury News

and then automatically assign abbreviated codes and country names to the news agency specified.

The final database should look something like this:

"Landslide kills 7 on Indonesia's Java - Reuters UK" - Reuters UK - REU - UK - 7

No one in my entire university can figure this out.

Can you please help????

Hi all,

What is the formula to be used to extract number from a alphanumeric string located at different row?

example:
test123 test128 test131
test124 test129 test132
test125 test130 test133
test126 test131 test134
test127 test132 test135
test128 test133 test136

result:
123 128 131
124 129 132
125 130 133
126 131 134
127 132 135
128 133 136

Thanks & Best Regards,
Chiwai

Hi there

I have several cells in a sheet from which I would would like to extract text from the middle of the string and concat the word actual on the end. e.g

b3 contains:

00 PRELIMINARIES - FORECAST

In the cell below that string, I would like to extract the word "preliminaries" and add the word "actual" to the end of it (result = "Preliminaries actual" (in sentence case is possible))

b5 contains:

15 STRUCTURAL STEEL - FORECAST

In the cell below I would like to extract the words "Structural steel" and add the word "actual" to the end of it (result = "Structural steel actual" (in sentence case is possible))

Some of the strings have two words I would like to extract and some only have a single word - if somenone could assist with a formula that would work in both cases it would be great.

Thanks in advance

Hi,
I would like to extract a text from a string. I found this post and it did what I needed however some of my cells have two values that need to be extracted example:
A1
http://www.excelforum.com/newthread.php?do=its+hot&ip=123.1.865&date=1208http://www.excelforum.com/newthread.php?do=its+hot&ip=133.1.965&date=1208
A2 http://www.excelforum.com/newthread.php?do=excel+rocks+my+life&server=powerful&ip=246.86.66
A3
http://www.excelforum.com/newthread.php?do=i+like+food&j=dasdas&ip=123.1.865&k=sadaasdhttp://www.excelforum.com/newthread.php?do=i+like+food&j=dasdas&ip=133.1.666&k=sadaasd
A4 http://www.excelforum.com/newthread.php?do=nice&ip=111.111.11&f=asdsad

so I would like to get
B1 123.1.865, 133.1.965
B2 246.86.66
B3 123.1.865, 133.1.666
B4 111.111.11
Any idea how to do so? Thanks

Dear Friends,
How can I extract the word from the following alphanumeric string:

example:
AB12
BDF45
RL4
MTQ345
V5

result should be:
AB
BDF
RL
MTQ
V

Thanks

Hello,

I'm looking for a way to extract numbers from a string.

The string can potentially have as few as one number or as many as 25. Each number is separated by a comma.

Here is the code that creates the string:


	VB:
	
 lstbox 
    For i = 0 To .ListCount - 1 
        If .Selected(i) Then 
            msg = msg & .List(i) & "," 
        End If 
    Next i 
End With 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
lstbox is a muli-select list box. So the string might look like this: "2," or it might look like this "1,4,6,9, 11," or "1,2,3,4,5,6,7,8,9,10,19". The point being there is no single structure for it.

The numbers will be used as i in a function where S = x^i. ( S is a base number, and x is a constant for inflation).

Thanks.

Hi. I've got a cell with codes, such as:
21ML,
43GKP,
etc.

I want these to look like :
ML21,
GKP43,
i.e. swap the numbers with the letters but keeping the order of the characters.

I can extract the number with :
This code is from Microsoft and works fine. But now I need something to get the text from the code, to then concatenate it
with the number. 

Any ideas?

I am new at this so if there is something I do in error please let me know.

Issue: I am trying to extract numbers from an alpha numeric string, that is greater than 10.

I found something on this site, which is great, but I came across an issue for my needs.
http://www.excelforum.com/excel-work...ic-string.html

=LOOKUP(99^99,--("0"&MID(F2,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},F2&"0123456789")),ROW($1:$10000))))

I would need something similar but I need the result to only take the set of numbers greater than 10

For example (I'll use something similar to what was previously given):
A1 = 251 chances
A2 = 1250chances
A3 = red2 howmany 1000chances
A4 = B4lue30chances
A5 = W1hite 222222

Result display:
K1 = 251
K2 = 1250
K3 = 1000
K4 = 30
K5 = 222222

Im not that great with excel, but still learning, if anyone can help I would appreciate it.

Dear all,

I have an issue: A string with text and symbols (/,-) exists in a cell and I want to extract text seperately into each cell in a column. For example:

Issue:

Cell A1: HAN/SGN-BKK/SIN

Desired Result:

Column A B

Row 1 HAN BKK

Row 2 HAN SIN

Row 3 SGN BKK

Row 4 SGN SIN

So, which formulas or macro can be used to obtain the above result? Please advise me!

Many thanks & Best regards/ LongNT

I am writing an application that automates excel 2003 to extract text from cells. How do I get all of the text from the cell and not just the first 255 characters?

I am using the Value2 member of the Range object. Any help would be great.

Hello,

I'm using Worksheets("LookupTable").Autofilter.Range.Address and this gives me:

$A$1:$D$28

How can I extract 28 from this string.

(I wan't to know the number of lines rows of my Autofilter.)

Thanks

ephie

Hi,

I want to extract data from array string and then sum the values. For reference attaching the excel.

regards,

---Yogi

Can I extract text from cells... without double-clicking in each cell and copying and pasting?

See attachment.

Thanks,

I have to extract numbers from a string. Example:
testing@hotmail.com(1-2),test@hotmail.com(3-4-5),testt@hotmail.com(12-17) How do you extract the Numbers only?
Thanks, Erwin

Hi!

Alphanumeric string: "Option Exercise at $22.09 per share."

How does one extract 22.09 from the above alphanumeric string?

I tried the array:
=1*MID(A1,MATCH(TRUE,ISNUMBER(1*MID(A1,ROW($1:$9), 1)),0),COUNT(1*MID(A1,ROW($1:$9),1)))
But this only returns the first whole number.

Thanks.


No luck finding an answer? You could always try Google.