# 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_11021_52350485brock3377

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?

## 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.

## Extract 2nd Number From A String

Feb 15, 2013

I wanted to return the second number in a string such as this:"0 of 0 jobs ... View all jobs"

The following worked perfectly:=MID(U3,FIND("f", U3)+1,LEN(U3)-FIND("f", U3)-23)

Until this line came up:"1 of 1 job ... View all jobs"

Obviously the "jobs" changed to "job" because of there being only 1, so the "-23" no longer worked. I need this to get the second number out of the string and it can change because the data is being refreshed from a website so it could easily change from one line to another.

## Extract The Last Number From A String?

Jun 20, 2014

It's for listing items to eBay. Ink Toners to be precise, the macro I have creates everything I need for a listing with a few msg prompts and importing data from another sheet. Saves me hours of work. Each cartridge has a Black, Cyan, Magenta, Yellow. The macro works fine for cartridge sets that go under one name. E.g: Brother TN325 Black, TN325 Cyan, TN325 Yellow... etc.

I've run into an issue with some cartridges which have different names: HP C530a Black, C531a Cyan, C532a Yellow...

I set the macro up to deal with different number, by removing the last character of the "tonername" string "C530", then replacing it with "C531".. etc.

What I haven't factored in is the ones with an A on the end. I need a way to tell it to ignore the a if there is one.

To make it clear, I will be inputting the text: C530a From this, I need to create 3 more strings: C531a, c532a and C533a

Here's the snippet of code I'm using to achieve what I'm already doing.

Code:
Selection.Replace What:=A, Replacement:=Left(y, Len(y) - 1) & "1"
Selection.Replace What:=b, Replacement:=Left(y, Len(y) - 1) & "2"
Selection.Replace What:=c, Replacement:=Left(y, Len(y) - 1) & "3"

[Code]....

## 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?

## 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.

## 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

## 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

## Extract 5 Digit Number From String

Jan 12, 2009

I need to extract a set of 5 numbers from a string of characters. The number set I need is always 5 digits long, always starts with a 1, and always begins after a period. But the number I need can occur in different parts of the string:

In each example below I would want to extract only the 10572 portion.

5194003.10572.N
5194003.10572.R
5194003.1.0010785.10572
7500008.N.5999900.10572

Additionally (if possible) if the cell is blank I don't want anything to show up, and if the string begins with a letter then I don't want anything to show up either.

## VBA Function - Extract Number(s) In String

Dec 3, 2009

My knowledge of functions is pretty limited, or negligible to be honest. I require a function to extract numbers from each cell in a selected range.

Eg.

cells contain the following data:

cell 1: xxxxxx 45,59
cell 2: x xxxxx xx 6,45,27
cell 3: x xxxx 28

were x represents text

I need to loop through each cell and extract each of the numbers and list them in a separate range. If applied to the above range of cells the function would show the below in the ouput range. Each number in its own cell.

6
27
28
45
45
59

## Extract The First 5 Characters In A String, And Convert Them To A Number

Nov 7, 2008

Say I have a string "09800EBHR052708"

How would I take the first 5 characters and get a number out of them?

## 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: ....

## Extract 7-digit Number From A Mixed String

Sep 26, 2009

I am trying to extract a 7-consecutive-digit number from a mixed string (letters and numbers). The only condition is that the number has to start with a "7" or "6" and be exactly 7 consecutive numbers long. My function only evaluates for one and one within the other but not both. It's faulted because it only looks for the occurrance of "6". rCell is the string range.

Function ExtractNumber(rCell As Range)

If Len(Mid(rCell, InStr(1, rCell, "6"))) > 6 Then
ExtractInvoice = Mid(rCell, InStr(1, rCell, "6"), 7)
ElseIf Len(Mid(rCell, InStr(1, rCell, "7"))) > 6 Then
ExtractInvoice = Mid(rCell, InStr(1, rCell, "7"), 7)
End If

End Function

## Extract Negative & Positive Number From String

Jan 15, 2008

I am using a regular expression in a function to extract numbers from strings. the code I am using is below.

Function ExtractNumber(rCell As String)

Set RegEx = CreateObject("vbscript.regexp")
RegEx.Pattern = "d*.d*"
If RegEx.Test(rCell) Then
ExtractNumber = CDbl(RegEx.Execute(rCell)(0))
Else
ExtractNumber = vbNullString
End If

End Function...

## 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

## 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.

## 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?

## 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

## 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

## 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)

## 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.

## 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.

## 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

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

## 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

## 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

## 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.

## 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).

## 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".

## Extract Number From Multiple Occurrence Delimiter Of String Excel

Aug 14, 2014

I'm trying to extract all the numbers from the left of the delimiter ":" . They can occur once or more.

E.g. cell F2 contains BP2.2.1:40 BP2.2.1:50 BP2.2.1:60 BP2.2.1:70 BP2.4.1:80

and what i want as a result is : BP2.2.1 BP2.2.1 BP2.2.1 BP2.2.1 BP2.4.1.

I've tried =IFERROR(LEFT(F2,FIND(":",F2)-2), " ") but only displays 1 out of a possible 4 in the cell.