Extract Text From Alphanumeric String

Jul 16, 2007

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.

ADVERTISEMENT

Extract Alphanumeric From Text String

Dec 26, 2013

I have the following text string.

What formula can I use to extract these results on the right?

Sample file.xlsx

View 8 Replies View Related

Extract TEXT From Mid Of Alphanumeric String Using Formula

Jul 18, 2012

I have another question regarding extracting Alphabet from the alpha Numeric String:

Column A

POT470G12B
PBY100G12
PCLN6400S12
PCX.US10X12
AAPL6000G12A

BP380S12A
BP390S12

The output i want from the string is :

Column A
Column B

POT470G12B
G

PBY100G12
G

[Code] ........

View 9 Replies View Related

Extract Number From Alphanumeric String ..

Dec 16, 2008

I would like to parse a part number.

ab24s-12-g4a

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 5 Replies View Related

Extract First Number In Alphanumeric String?

Oct 11, 2011

Have many rows of alphanumeric strings. 2-4 letters followed by a variety of numbers, some spaces, and some additional letters. What I need is to return the first number after the initial 2-4 letters, everything after that first digit is junk. Thus, I would like a formula that takes me from:

AB23400 12 -> 2
CET25800 1 -> 2
NYT67300 H01 -> 6
HSRF49900 1 -> 4

View 9 Replies View Related

Extract Number From Alphanumeric String Located At Different Row

Jan 20, 2008

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

example:
test123test128test131
test124test129test132
test125test130test133
test126test131test134
test127test132test135
test128test133test136

result: ....

View 11 Replies View Related

Extract Last Letter And Remaining Characters From Alphanumeric String

May 14, 2013

Having trouble with this one. Searches seem to bring up every other variation of extracting info from strings except this.

I have cells which contain alphanumeric strings as below and, using a formula, I want to extract everything from and including the last letter to give A 2-3, B 3 and C 3-4 in the examples below.

Sub-base A 2-3
Sub-base B 3
Paving C 3-4

View 7 Replies View Related

Extract TEXT From Alphanumeric Cell

Oct 23, 2009

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.

View 6 Replies View Related

Extract Numbers From Alphanumeric Text

Feb 19, 2008

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 9 Replies View Related

Match All Text And Closest Number In Alphanumeric String?

Feb 21, 2014

Formula to match all text and closest number in alphanumeric string

View 1 Replies View Related

Extract Text From Given Point In Text String When Data Points Do Not Share Given

Jul 9, 2014

I have a set of task descriptions that I am attempting to trend on. Some of these (Column B) have the customer's name added to the description; others do not. I need to be able to make a list of task names (ColumnA), removing the name from the text string.

The formula I am using is [=LEFT(B3,FIND("for",B3)-2)].

The problem I am having is when the description does not contain the "for" built into the formula, I get "VALUE" error. What adjustment can I make to the formula to pull over the Description if the "for" does not appear in the text string?

View 3 Replies View Related

Extract Multiple Text Strings From A Long Text String

Sep 4, 2009

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 9 Replies View Related

Extract Text From String

Dec 10, 2007

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.

DDR_WF602_____02_00_001_FLANGE_WR34_4_HOLE_PLAIN_CLEARANCE

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 9 Replies View Related

Extract Text From A String

Sep 13, 2013

I have thousands of address fields that look like this:

100 Broadway, Suite 1101 New York, NY?10005 United States

Sector 30, NH-8 Gurgaon, Haryana, ?122002

61 Broadway 17th floor New York, NY?10006 United States

11 Beacon Street, 3rd Floor Suite 305 Boston, MA?02108 United States

88 7th Avenue New York, NY?10109 United States

600 Pennsylvania Ave SE Suite 220 Washington, DC?20003 United States

1601 Elm Street Suite 3900 Dallas, TX?75201 United States

50 Federal Street Suite 600 Boston, MA?02110 United States

I need to separate the street address, city, state, zip and country.

View 4 Replies View Related

Extract Text String

Jan 31, 2010

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 9 Replies View Related

Extract Text From A String

Aug 21, 2006

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("A2").Select
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 8 Replies View Related

Generating Random Number String Based On Alphanumeric String?

Aug 14, 2014

Wondering if it is possible to generate a random 4 digit number based off an alphanumeric string?

Example;

Cell A1 has 123XVF1234
Cell A2 has 321AFW4321

In B1 I would like to have a 4-6 digit number that is generated based on the alphanumeric data in Cell A1 (and so on down the list). If that is possible, I would also need to be able to convert back the 4-6 digit number back to its original alphanumeric value

Example;

If B1 returns 643562 it would need to be able to be converted back to 123XVF1234

View 7 Replies View Related

Extract Number From Text String?

Apr 28, 2014

Below is a sample of data I need to extract the 8 digit number:

WO_32092_56228491_575482-113476-WP55
WO_32092_56228491_115130-WP55
WO_32092_56228491_115116-115118/115117-WP55
WO_32092_56228491_291881-318903-WP55
WO_259_56748761
WO_13895_52245652COUNTYRDN-30
WO_17368_51421730-A-ADDPOLEINFIELD
WO_17368_50885431-A-ADDMISSINGPOLE
WO_11021_52350485brock3377
In a spreadsheet, I had set up three columns where:

A: removed first three characters. WO_32092_56228491_115130-WP55 to 32092_56228491_115130-WP55
B: removed up to the _ . 32092_56228491_115130-WP55 to 56228491_115130-WP55
C: captured the first 8 characters left. 56228491_115130-WP55 to 56228491

I am pulling the data into Excel via an ODBC where there is thousands of rows of data. The three column process puts a tremendous strain on the processor.

Is there a formula that will extract the 8 character number without a three step process?

View 3 Replies View Related

Extract Data From Text String?

Jul 16, 2014

I have an example sheet attached with the value I need manually typed in B and C.

What I would like is the formula to do this without me having too manually (as the full workbook has over 5,000 lines)

The ID will be between 1 and 4 digits and always in the same position

The name cane vary in length, and also be in a different position (depending on the length of the ID)

View 14 Replies View Related

Extract Parameters From A String Of Text?

Dec 11, 2013

I'm trying to find a formula which in essence is the same as 'text to columns'.

I'll be pasting in post strings such as: A=B&C=D&E=F etc etc.

I need a formula to break the url so i have all the individual parameters listed i.e

A=B
&C=D
&E=F

Can this be done without a macro? Also i'm not fussed where the '&' belongs either at the front or the end of the text string.

View 5 Replies View Related

Extract Numbers From Text String

Sep 24, 2009

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 2 Replies View Related

Extract Numeric Value From Text String

Feb 6, 2012

I’m wanting to extract the numeric values & piece of text from the end of a text string. Example data set starting in A1;

Big Box Dom 40*20

Result wanted :
Column B1 : Big Box
Column B2 : 40*20
Column B3 : 40 x 30

View 3 Replies View Related

Formula Can I Use To Extract This String Text

Dec 8, 2006

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 9 Replies View Related

Extract A Portion Of A Text String

Jan 1, 2007

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
Eg:-

CHQ 123456
CHQ 187546
CHQ 745865

View 9 Replies View Related

Extract Text After Space In String

Oct 5, 2006

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 6 Replies View Related

Extract Part Of Text String

Aug 10, 2007

CREATE TABLES LIKE BELOW?Column 1 Column 2
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 9 Replies View Related

Extract Numbers After Text In Text String

Mar 12, 2014

I am trying to extract numbers after a specific text in a text string, for eg :abc SN 12345 xyzedf SN No. 456 mnoAs per above, i want to extract any numbers afters "SN". the numbers can be vary in digits i.e. it can be 3 digit numbers or 4 or 6. Also, at times there is some other text in between (like SN No.) numbers and search word (i.e. SN)Any formula to get result as "12345" and "456".

View 5 Replies View Related

Extract Data From A Text String By Using Formulas?

Aug 4, 2014

I am trying to extract data from a text string by using formulas.

View 6 Replies View Related

Extract Text From String (complex Rules)

Feb 19, 2012

I have a column with data in the following (ideal) format:

texttextexttext/s2
texttextexttext/s2/c14
texttextexttext/s24/c2

and need a VBA solution to automatically split it across 3 columns, i.e

column1 column1 column1
texttextexttext s2
texttextexttext s2 c14
texttextexttext s24 c2

/s will always be present and will always be followed by one or more digits (no limit).

/c may or may not be present but if it is present, again it will always be followed by one or more digits (no limit)

I have been using TextToColumns with "/" as the delimiter and it works fine in most cases. However, the data frequently contains additional "/" characters which breaks everything. some examples of valid but problematic data are:

texttext3/12/2012text/s2/c2
text/textexttext/s2/c2

and even:

text/stext/ctext/s2/c2 (i.e. multiple occurrences of /s or /c)!!!

View 9 Replies View Related

Extract Data From Text String In Cell

Apr 13, 2012

I have a worksheet with over 10,000 records. The column that lists where a person is willing to relocate can have up to 60 city/state entries in one cell.

Here is an example of what appears in one cell - this is exactly how it appears:

ASAI Los Angeles (XX , CA
DFO Pacific (XX ONLY), CA
DFO Pacific Area Analyst Laguna Niguel (XX ONLY), CA
SAI Los Angeles (XX ONLY), CA
Ldr Los Angeles El Segundo POD (XX ONLY), CA
Ldr Los Angeles Long Beach POD (XX ONLY), CA
Ldr Los Angeles POD (XX ONLY), CA
Senior Ldr (XXXX) Washington (XX ONLY), DC

What I need to do is be able to sort on city and state, so I wanted to be able to extract and separate the city and state. I tried using a find/replace (CTRL J) to enter a semicolon between each entry and thought I could do text to columns to separate, but that doesn't work.

How I could extract this information? Notice that the first entry is missing ) - that is throughout the records.

View 7 Replies View Related

Extract Numbers From Specified Place In Text String

Feb 19, 2008

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 9 Replies View Related

Formula To Extract Word Out Of Text String In Cell?

May 7, 2014

I have a long list of process steps in a collumn e.g.

A
_Tank1_CIP
_Tank1_CIP
_Tank2_CIP
_Tank4_CIP
_Tank_9_CIP

and then i have a list of tanks: Tank1, Tank2 etc. The i want a forumla to extract and return the tank in a adjacent cell:

A B
_Tank1_CIP Tank1
_Tank1_CIP Tank1
_Tank2_CIP Tank2
_Tank4_CIP Tank4
_Tank_9_CIP Tank9

View 6 Replies View Related

Extract And Copy Part Of Text String From One Cell To Another?

Aug 22, 2014

I have to sort list of thousands name (3000 names) and remove duplicates. My problem is majority of the names have their title (i.e) Prof. Dr. Ir. Sir. etc typed in, so I need to copy the title in different cell and have their name only. Here's what I expect:

Current List ----> Column A -->Column B
Drs. H. A. Andrew Boston, MRE. ----> Drs. H. --> Andrew Boston, MRE
Drs. H. Andrew Smith ----> Drs. H. --> Andrew Smith
H. Abd. Mohammed Junus ----> H. --> Abd. Mohammed Junus
Prof. DR. Jane Doe, MD ----> Prof. D --> Jane Doe, MD
Prof. DR. Ir. H. Randy Wong, MBA --->Prof. DR. Ir. H. -> Randy Wong, MBA
Dra. H. A. Karen Patel ----> Dra. H. --> Karen Patel
Drs. H. A. M. Kangkong ----> Drs. H. A. --> M. Kangkong
Prof. Drs. H. A. Kareem Saleh, Ph.D -> Prof. Drs. H. A. ->Kareem Saleh, Ph.D
Dra. Hj. Nina Schorder ----> Dra. Hj. --> Nina Schorder
Ir. Abdul Jabbar ----> Ir. --> Abdul Jabbar

View 1 Replies View Related

Column Array Text String Match And Extract?

Aug 15, 2013

So, using an array under the Names column, search the string under combined names and break apart as illustrated. Must stay on the same line...w/o VBA.

Combined Name(A)
Names(B)
LeftofName(C)

[Code].....

View 1 Replies View Related

Looking To Extract Numeric Value With Two Letters Attached To It From A String Of Text

Jul 20, 2008

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.

BEANO AD A-G LIQ RS 0.51OZBEANO AD A-G TAB-CHW RS 12CTBEANO AD A-G TAB-CHW RS 30CT

View 9 Replies View Related

Extract Number Of Variable Length From Text String

Jan 25, 2007

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 5 Replies View Related

Extract 1st 2 Words & Middle Section From Text String

Jun 18, 2008

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 9 Replies View Related

Extract Middle Words From Text String With Multiple Dots?

Oct 22, 2012

black.blue.red.yellow.green.white

I'd like to extract just "blue.red.yellow.green" to the following spreadsheet column, keeping the intermediate dots, but getting rid of the first/last words and their succeeding or preceding dots.

I've tried using RIGHT, LEFT, MID formulas unsuccessfully

View 3 Replies View Related

Using Automated Index Sheet To Summarize Catalog - Extract Text String To Define Cell

Jun 10, 2013

I have a built a catalogue of records for various of my companies assets. Each asset has its own sheet in a work book and I have used a vb macro to include an automatic Index sheet at the front of the book. This takes the names of the various tabs, in the order I have them in the workbook and creates a simple listing in column A as an Index List

What I want to do is to build a summary table to the right of the Index List, pulling in a few of the key data points about the assets into a single table.

To do that I need to be able to extract the text string from the Index List and use it to form part of a cell reference pointing to a specific cell in the relevant individual Asset sheets.

View 4 Replies View Related

Extract Numbers From Alphanumeric Strings

Mar 7, 2009

I have this formula that extracts numbers from alphanumeric strings.

{=1*MID(A1,MATCH(TRUE,ISNUMBER(1*MID(A1,ROW($1:$100),1)),0),COUNT(1*MID(A1,ROW($1:$100),1)))}
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 9 Replies View Related

Extract Numbers From Alphanumeric After Specified Character

Jul 24, 2007

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 6 Replies View Related

ADVERTISEMENT