Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
  Advanced Search
  HOME    TRACKER    Excel


Extract Text From Alphanumeric String

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.

View Complete Thread with Replies

Sponsored Links:

Related Forum Messages:
Extract Number From Alphanumeric String ..
I would like to parse a part number.


if there is a "-#-" in the string, that is the value I want. If the "-#-" is not present, I would like to take the first number. Currently, as written in your first example, I am getting the first number - excellent and thank you!

In the sku, the first digit reprepresents capacity. If a second number is present after the capacity portion of the sku (i.e. "-#-"), that means that they want to order it filled with less than capacity. For instance, a carton of eggs and they want 6 eggs instead of the max capacity of 12. (i.e. egg12-6-z2z). Numbers after the second octet (i.e. z2z) do not matter.

It would be a number only in the second octet. If there are characters with it, the equation should disregard it.

Here are the scenarios, and what the formula should accomplish

1) egg12 --> currently returns 12
2) egg12-6 --> would return 6
3) egg12-6-g2c --> would return 6
4) egg12-g2c --> would return 12

View Replies!   View Related
Extract Number From Alphanumeric String Located At Different Row
What is the formula to be used to extract number from a alphanumeric string located at different row?


result: ....

View Replies!   View Related
Extract Numbers From Alphanumeric Text
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: ...

View Replies!   View Related
Extract TEXT From Alphanumeric Cell
I've got a cell with codes, such as:


I want these to look like :


i.e. swap the numbers with the letters but keeping the order of the characters.

View Replies!   View Related
Extract Multiple Text Strings From A Long Text String
An original text string entry appearing in an Excel cell would be:

"N7C Neuroprotective J5Z Antiviral, other M2Z Antiarthritic, other J5A Antiviral, anti-HIV"

I need to extract N7C, J5Z, M2Z and J5A from this string and list these alphanumeric values in separate cells adjacent to the original text string. The challenge is that these alphanumeric references may appear in different positions within the original string with no fixed value e.g. a "," separating them. The alphanumeric references may also be 3 or 4 characters in length and there may be different numbers of alphanumeric references in the original string.

Another example would be (very different from the first):

"T2Z Recombinant, other K5B Radio-chemoprotective J3Z Antibacterial, other D3A Vulnerary A10C Symptomatic antidiabetic K6Z Anticancer, other R8A Antiasthma B6A Septic shock treatment I1Z Immunostimulant, other S1Z Ophthalmological R8B Antiallergic, non-asthma M1A1 Anti-inflammatory"

You can see that in this further example "A10C" & "M1A1" are 4 character alphanumeric strings wheras the others feature 3 characters.

View Replies!   View Related
Extract Text From String
I need to be able extract a string from within a longer string. The information I want will always start 5 chracters in and be bordered by an underscore either side, but could be of any length. For example in the following string, I would want to extract WF602.


I've been looking at the RegExp functions but can envisage problems with this as later parts of the string (WR34) have the same form as the part I need.

I can get rid of the first 4 characters using

View Replies!   View Related
Extract Text String
Is there a relatively simple excel function which will extract the end of a text string. The end has two or three full stops / periods so I would like to count back three to six or more characters and use the full stops as 'cut off points' by passing other full stops?

Column1 Column 2simon.123Function hereAnswer = .12.3dan.123456Function hereAnswer = .123.456andrew.1234567Function hereAnswer = .123.45.67

View Replies!   View Related
Extract Text From A String
I would like to extract whole word according to starting string.
I wrote following code, but function " find" is not exist.

Sub GetFullName()
Dim str As String
str = InputBox("Requested String?")

Range(Selection, Selection.End(xlDown)).Select

For Each rng In Selection
rng.Offset(0, 1) = Mid(A2, Find(str, A2, 1), Find(" ", A2, Find(str, A2, 1)) - Find(str, A2, 1))
Next rng
End Sub

View Replies!   View Related
Extract Part Of Text String
8 6CSS130 + 2CSS200g x10hanger 200 GM
250ml B2G1 Towel in bagx24 0607 250 ML
18 g-Mint 18 GM
100g 100 GM
150GM 150 GM

i have a data as above, i wanna to extract the 250ml from the column 1, only 250ml (basically i want to extract the size code like 150g, 100gm from the data above).

View Replies!   View Related
Formula Can I Use To Extract This String Text
What formula can I use to extract this string text.

I need to be able to extract the name after "and"

I would like to extract the name Lana from:

View Replies!   View Related
Extract A Portion Of A Text String
I have a text strings that contains the word "CHQ 123456" or some other CHQ number. There might be another word/words after the word CHQ xxxxxx something like:

Payment by CHQ 123456 against your inv 45225
ABCD Company Limited - CHQ 187546
PO 4520061257/CHQ 745865/invoice Number 4125

I need a UDF that extracts the CHQ No. from the text string

CHQ 123456
CHQ 187546
CHQ 745865

View Replies!   View Related
Extract Numbers From Text String
I have several lines with text strings containting three numbers in each line. I need a code to extract all three numbers from each text string. The numbers can be placed on the following columns in each row.

View Replies!   View Related
Extract Text After Space In String
i have used the RIGHT function to remove the 1L/1S and the 1L from the samples belows

the problem i have is when the 1L is in its cell there are 3 spaces before it
how can i remove those spaces so that all the data is to the left of the cell

BYA001 1L/1S
BYA003 1L

View Replies!   View Related
Extract Numbers From Specified Place In Text String
I have got cell A1 containing this text string:

=IF(SUM('SL-001 - AT-001-001'!R[852]C:R[856]C)=0,SUMPRODUCT('SL-001 - AT-001-001'!R[826]C:R[830]C,
'SL-001 - AT-001-001'!R[840]C:R[844]C,'SL-001 - AT-001-001'!R[846]C:R[850]C),
SUMPRODUCT('SL-001 - AT-001-001'!R[826]C:R[830]C,'SL-001 - AT-001-001'!R[840]C:R[844]C,
'SL-001 - AT-001-001'!R[846]C:R[850]C,'SL-001 - AT-001-001'!R[852]C:R[856]C))
*'SL-001 - AT-001-001'!R992C*R3C9
and I would like a macro that will extract the numbers between each instance of the letters R and C , i.e. 852, 856, 826 etc etc. in cells A2, A3, A4 respectively.

View Replies!   View Related
Looking To Extract Numeric Value With Two Letters Attached To It From A String Of Text
Looking to extract a numeric value with two letters attached to it from text strings. The text strings vary. Below are the examples I'm looking to extract .51OZ from the first cell, 12CT from the second, and 30CT from the third.


View Replies!   View Related
Extract Number Of Variable Length From Text String
Imagine that I have this list of thousands of customers, who are listed in column B in the format "customer no+space+customer name".

Now I want to extract ONLY the customer no from the text string in column B and insert it as number value in column A on the same row.

Would have been easy with "Left" command, but as the customer numbers have variable length, I have a problem.

View Replies!   View Related
Extract 1st 2 Words & Middle Section From Text String
I have a text string with 3 data to be extracted and then split throu range, but only specific parts is needed.

string is like that:
Name Surname - Number : ################ - Exp : YYMM - Centre etc etc....

Lets say its allways happens in cell A3, for example:
Adrian Kukuwas - Number : 1235467890123456 - Exp: 1009 - Centre... I would like a macro:

*that runs only if string starts with a letter, then if so
*takes Adrian Kukuwas to cell D3
*takes 1009 (the exp) to B3 and makes it look like 09 10 (or 09_10)
*in A3 leaving only the number 1235467890123456 text formated (sometimes the copy source formats the cell for bad)

View Replies!   View Related
Extract Numbers From Alphanumeric After Specified Character
Say for example I have ABCD-ABC12 basically an arbitrary length of alpha (A-Z) characters followed by an hypen "-" followed by another arbitrary length of alpha (A-Z) characters and then
immediately followed by an arbitrary length of numbers. (with no spaces between alpha and number)

How can I extract just the numbers from the group of alphanumberic characters after the hyphen
and set it to a LONG variable?

View Replies!   View Related
Extract Numbers From Alphanumeric Strings
I have this formula that extracts numbers from alphanumeric strings.

However this extracts only the 1st instance of the numbers

In a string like 123avfbsdf4556.. it'll extract only 123.

My questions are the following:

1. Is there a way that i could get the result as 1234556

2. A way which refers to a cell where I put in a number and it'll extract those many number instances. In the above example, if I put the number as 1, it'll extract 123. If I put the number as 2, it'll extract 4556 and so on.

I guess this would require some modifications to the Match function so that it does not look at only the 1st instance.

View Replies!   View Related
Alphanumeric Characters Extract Only The Numerics From Cell
I have the following in Column A


I'd like have this result


How would I achieve this.

View Replies!   View Related
Extracting Alphanumeric Value From The String
I have a rather difficult task to accomplish. What I need is to be able to extract an alphanumeric value from the string. It is a serial number of the equipment and it is always nine alphanumeric characters long. The problem is it can be anywhere in string. Example: “2156545 36 month lease NWL023568 Nancy Clay”. The serial number I need to extract is NWL023568.

View Replies!   View Related
Non-zero Numbers From An Alphanumeric String
I am looking for formulae to identify the first 20 non-zero numbers from a string of alphanumerical and non alphanumeric characters

EG If column A any data such as:

Then the corresponding row for column B would return 123

Using help from my maths Prof and some IT bods, i have so-far come up with the array-formula:


assuming that the total string is not more than 100 characters.

View Replies!   View Related
Remove Non-alphanumeric Characters From A String
I am trying to remove all characters that aren't letters or numbers from a string. Is there any way to differentiate between a non-alphanumeric characters and alphanumeric characters? I'm thinking of something like "ISTEXT()" that I could use on one character at a time. Or are there any wildcards I could use in the Replace function?

View Replies!   View Related
Increment Numeric Value In Alphanumeric String
I have created a macro that searches for an existing part in my worksheet and copies the found part to the row above. I would like to increment the value of cells B and H in the new copied row. Both cells contain an alphanumeric string that ends with a numeric value. For example cell B has a string like this "APL-DK0030" and cell H has a string like this "Dell Optiplex GX260 V09"....

View Replies!   View Related
Remove Numbers From Alphanumeric String
I have list of 15000 of Alphanumeric data for ex. ADEDO125ADSD589ADF121,UIEIROIWS12556ERE545,ADAS15455212AD4564AD2
And so on. Now I want to extract word from this alphanumeric. And i want to use only formula not MACRO OR VBA

View Replies!   View Related
Search Range Of Cells For Alphanumeric String
I tried to find out how to post my table but the link in that thread lead me to a notice saying I wasn't allowed access to the page.)

Cells A2:G2 contain various alphanumeric strings.

If any cell contains "SD#" (# = a number which might include decimal places) then I want to extract the number.

If more than one cell contains "SD#", I want to add the numbers together. The total goes in cell C6 and it needs to change if the contents of A2:G2 change. E.g. if A2 shows SD4.75 and B2 shows SD8, the total in C6 would be 12.75

So I'm looking for a formula for C6....

View Replies!   View Related
Extracting Letters And Numbers In A Random Alphanumeric String
I've got this problem: I need to separate around 40 alphanumerical entry in Column'A' to Columns'B','C','D','E'..

View Replies!   View Related
Arranging An Alphanumeric String In Alphabetical Then Chronological Order
In Cell A1 i have a text string of "ABS9E8C2D" i want something is cell B1that will arrange the string in Alphabetical order for Letters, then increasing order for numbers "ABCDES289".

View Replies!   View Related
Extract Alpha From A String And Compare With Another String
I have a problems here. The problems is attached in the file. I wanna extract alpha/char from a string. Example: I wanna extract the words "(M)" with the bracket from the string "Toothbrush (M)" in column A. After extracting the (M) out, I wanna do a validation to compare the (M) in column B with another data in column C, if the (M) is same as the data called "Medium" in column c, the validation will return "Match" in the column d!

View Replies!   View Related
Alphanumeric Counter (automatically Populate A Column With An Alphanumeric)
I need to automatically populate a column with an alphanumeric MFR0001 to MFR9999 and am currently using the formula below to add 1 to the start point (A1)...

Formula in A2 =LEFT(C1,3)&(RIGHT(C1,4)+1)

This works perfectly if I start at MFR1000, but breaks and drops the 000 if I use it on the number I need to start with (MFR0001).

View Replies!   View Related
Subtract From Number On Right Of Alphanumeric Text
We use excel to organize salaries and contracts for my fantasy baseball league. I need an easy way to go through all the teams and subtract one year from each players contract. This is an example of a team.

The contracts are in the columns like this NG-3

I would like to change that to NG-2

View Replies!   View Related
Sort AlphaNumeric Text By Numbers
Take for example ABC10 in Cell A1, ABC199 in cell A2 and ABC9 in cell A3

How am i able to sort such that ABC9 appears in cell A1 followed by ABC10 in cell A2 and ABC199 in cell A3?

View Replies!   View Related
Increment Number Within Alphanumeric Text
Is it possible to increment / decrement cells using wild characters? I've found myself needing to increment values at different position, for e.g. :


where I would want to be incrementing # placeholders. It would help if I can give it a start number, and the increment amount (which can be different than 1).

What about possibly extending it a bit more to include two or more place holders at the same time as follows:

ABC##- FR*** (where # would have its own starting number and increment value, and * would have its own).

View Replies!   View Related
Pull Numbers From Alphanumeric Text
The title of this thread is the best way I can describe what I wish to accomplish. You see, I have a column of information requests in an Excel spreadsheet, each of which his tagged with an alphanumeric request number, and I wish to sort and/or perform and min/max function on the request identification. However, each ID has a IDR in front of it - for instance, IDR000452 - and the letters make the system read the contents as text. Does anyone know of a formula I can use to create a new cell which contains only the numerical contents of the above example?

View Replies!   View Related
Stripping Text From AlphaNumeric Strings
I'm working on an attendance spreadsheet that shows the employee's absence using the format "X####", where X can be one of the following three values: S(for sick time), P(for personal time), and V(for vacation); "####" is the number of hours absent (can be from 0.25 to 8.0,measured in 15-minute increments, # can be either a digit from 0 to 9 or a decimal point, and there can be from 1 to 4 characters, but must be >0(I'm not worried about data validation yet).Examples are P8.0, V.5 (V0.5), S2.75,etc.

The spreadsheet is laid out such that each row represents an employee, and each column is a workday. After the employee's information are three columns labeled P,V, S. I'm trying to generate a formula that when applied to an employee's row, will display a running total of the absent hours based upon personal, vacation, or sick time taken. We'll call the range "CAL"(Short for calender)

To strip out the numeric characters on the right, I use the formula RIGHT(CAL, (LEN(CAL)-1)). To Determine which column the data should be summed in, I use the formula LEFT(CAL,1). Blank Cells are permitted, in fact most of the row will be blank. Now that I've figured out how to strip out the alpha from the numeric, how can I get the totals? I apologize for writing "War and Peace," but I wanted to be as concise as possible-

View Replies!   View Related
Extract Last Name From A Name String
I have a list of names in a data set like the examle below. I would like to be able to isolate the last name so I can match the data in this data set with a data set with additional information. The only common set of data I have is the first and last names.

View Replies!   View Related
Formula To Shorten An Alphanumeric Line Of Text
I'm using the formula '=LEFT(A2,4)+0 to shorten a numbered line of text.Is there a way to adopt that formula to do the same thing to an alphanumeric line of text (i.e. 80A0267AA1 to read 80A0267) or even better substitute a "-" in place of the "A" (i.e. 80A0267AA1 to read 80-0267)? I know this is really picky, but I figure it's worth a shot.

View Replies!   View Related
Return Value Based On Last Number In Alphanumeric Text
I want to be able to return a text string of either Trainee 1 or Trainee 2, (or default to other text) based on codes that are 4 digits (DD01, KKB1, KKB2, KKC2, GG03, etc, where starting with K and ending with 1 or 2 is the criteria for TR 1 or TR 2 . There are two many KK** items to list them all in an If statement and I can't seem to use a wildcard.

=IF (right(c1,1) = "1", "Trainee 1", B1)

gets me halfway there I think but of course doesn`t pick up Trainee 2 and also returns Trainee 1 when it sees DD01.

View Replies!   View Related
Increment Numeric Part Of Alphanumeric Text
I have a cell with a value of, Text 1 and in the next field I want it to display Text 2, then text 3 and so on.

Is there any way of doing this? I guess I am looking for something like a1+1, just a shame it doesn't work.

View Replies!   View Related
Convert Decimal Alphanumeric Text To Whole Number
i wanna convert the decimal number to a round 2.0L to 2L.

View Replies!   View Related
Pull Number By Position From AlphaNumeric Text
I am having an issue with formatting a cell to 'general'. I have a macro that runs using a formula (i.e. =MID(A12, 18,5)) to extract certain characters from an external data source. I then use only the values of that formula. There then is another macro that compares that value to another value in another worksheet. The issue I am having is when the macro runs, the code is read as ActiveCell.Offset(0, 3).Value...but instead of returning the value in 'general' format, it returns it with what appears to be text (i.e. "75.50" instead of 75.5). This acts as a mismatch since the quotes appear.

I have attempted several things to correct, but even if I try to format-->general it maintains the quotes. The only way it changes is if I click within the cell...then it turns to general (i.e."75.00" becomes 75.5). I am not sure what is the issue.

View Replies!   View Related
Extract String From Cell
In the following rows I want to extract the string that ends in K (ie everything after the final space). But I don't know the starting position or how long the string will be.

C2 9K
C1M (RB57) 4K
C2 4K
C6 FM (RB78) 120K
C2 9K
C1M (RB57) 3.5K
C2 (RB68) 12K

Can this be done?

View Replies!   View Related
Use Mid To Extract String
I need to extract the term between the two colons: Snowboard Jackets

This term resides in AB289-
Snow : Snowboard Jackets : Guys SoftShell

Using MID, but not able to get all the term

View Replies!   View Related
Extract Alphanumerics From String
I have many strings of arbitrarily length. Each string always
has one number (0-9) component and one alpha-character (A-Z) component.

The order of the components in the string is entirely random.

Sometimes numbers come first and sometimes letters/characters.

Also, the length of each separate component is also varies and
there is no fixed rule to how long it will be each time.

Examples are:

String: RGH45 Alpha: RGH Num: 45
String: 4589THF Alpha:THF Num: 4589
String: FGGFFF56464645464 Alpha:FGGFFF Num: 56464645464
String: sdgdfgdfg874645 Alpha:sdgdfgdfg Num: 874645
String: 54sfsdfdsf Alpha:sfsdfdsf Num: 54
String: s54654646 Alpha:s Num: 54654646


I want to have two strings. One called Alpha and One called Num
I want to have the alpha-character component to be extracted from the string
and set to Alpha and the numerical component to be extracted & set to Num.

How/what is the Excel Macro VB code to accomplish this from within the macro?

View Replies!   View Related
Extract First Character Of String
I have a column that has various codes such as "E123" and "X456" and "S345". I just want to extract the numbers and leave the letters in each cell. How do you write a formula using the LEFT function to extract the last three numbers?

View Replies!   View Related
Extract Extension From String
How to Extract Extension from String?

"testing.xls" to "testing"
"testing123.html" to "testing 123"

View Replies!   View Related
Extract From String Using Wildcard
how do i extract a part of the text using wildcard(example "ID=") in the string in the whole columns and copy to other columns or delete the unwanted.

Below is a example which i create for better understanding.


I don't know if this break the rules, Please kinda forgive if i do as it quite urgent. I'll delete if it don't comply with the breaks.

View Replies!   View Related
Extract Data From A String
I have a string (as below - Call them A1:A4) which I would like to seperate into 4 columns (Call them B1:E4).

I have successfully seperated the first part using MID (It's always 5 digits) but the second part has a varing length which then impacts on the third and fourth parts of the string.... Any ideas?

87261 WIMBLEDON 10:08 10:10
87169 NEWMALDEN PASS 10:13
87171 SURBITON PASS 10:15
87177 HMPTNCTJN PASS 10:16

To add to this I am using the POCKET PC version of Excel which does not have all functions so at the moment I am limited to which functions I can use (Can you add functions to the PPC?).

View Replies!   View Related
Extract Values From A String ..
I have the following in Cell B2 :-


How can I extract with VBA the ...

24 < this could be 3 digits
6 < this could be 2 digits
3 < this could be 2 digits
6 < this could be 2 digits
163 < this could be 4 OR 5 digits

... so they go in Cells E6:E10 please. There will always be a comma and the final figure will always follow the = sign.

View Replies!   View Related
Copyright © 2005-08, All rights reserved