Tracker
HOME    TRACKER    Excel

Extract Two Digits From Number

I want to extract the fifth and sixth digits from an 8 figure number i.e. 20010891 would equal 10. I have tried every combination of Left & Right combined with Find that I know and nothing works.

Related Forum Messages:
Number Formatting: The First Three Digits Will Be Separated And Then Subsequently 2 Digits
i need to format my numbers in the following format

10,00,000.00

the first three digits will be separated and then subsequently 2 digits

Extract Digits From Text
I have a string in a cell that contains a name, a 3-4 character alpanumeric code and then a 4-6 digit number, each separated by a space. An example or two:

J Bloggs SPEC 123456
J Smith AG06 9364
J & K Brown SWP 358686

I need to be able to have in a new cell the 4-6 digit number at the right of the cell. give a formula that will separate the data I need? I've tried numerous ways without success.

Extract Numbers Based On First 2 Digits
I would like to extract numbers based on the first 2 digits (22....) of a column along with its next column (on the right) and put them on another sheet.

Regular Expressions, Extract DIGITS
I need to extract DIGITS from following: C23, C24, C515-C519. It may be done with regular expressions. Does anybody have experience with VBA code for RegEx?

Input: C23, C24, C515, C519.
Output: 23,24, 515, 519

Formatting A Number Field To Hold A Specific Number Of Digits
I wrote an excel program in Excel'03 for a dental office to manage state assistance patients and one of the table columns is the state assistance number. The problem is that the program defaults to a number format when it is entered instead of a text format, which is what I want. The issue is that state assistance numbers are always 8 digits and when it defaults to a number field the program drops the required preceeding zeros.

For example the number 00123456 will sometimes show up as 123456, which is wrong. It's like sometimes it will show up right and other times it gets a butterfly in its brain and deletes those zeros irritating the receptionist here. So, how do I get the table to either stay in text format or set up a number format that keeps the preceeding zeros?

Split Number To Digits
Can a vba macro be provided for splitting a number into digits? The number will be in Sheet 1 but splitted number will be on sheet 2. Splitting of numbers means a number entered into a cell will be splitted into different column/cells with one digit per cell.

Display First 4 Digits Of A Number
I work in a finance department and we have MANY numbers consisting of 7 digits. Is there a way to enter in the entire number, but only display the first 4 digits?

Large Number Loosing Last X Digits
I have problems in PASTING my 19 digit number from the source report into excel.

E.g, the original value is 8321515222222123122 but it always transfer to 8321515222222120000

I have tried the simple cell format setting, that is after pasting, I set the cells to text, but it doesn't help and also try the custom fomat "###################", but it is still the same result with the last 4 digit lost.

What I want the format to be is not in scientfic and have to be full display.

Specific Number Of Significant Digits
Does anybody know of a special format or a rounding function that displays or rounds a number off to a specific number of significant digits.

Lets say I would like 3 significant digits this would be the results:

0.40019 -> 0.400
6.543 -> 6.54
12.543 -> 12.5
32 -> 32.0
564.7894 -> 565
54387 -> 54400 (or 5.44E+4 to be correct, but i can live with 54400)

Check Number Uses Unique Digits
I would like a function that checks an input number to see if it contains unique digits. If the digits are unique the output is 1 else the output is -1. Thus, supposing we call the function UNIQ(), we find UNIQ(15423) = 1 but UNIQ(154532) = -1. The input is always a positive integer.

Add Space If Number Is X Digits
In my worksheet there is a column with values of either 3 or 4 numbers. If the value only has 3 numbers I would like to add a space before the first number, to ensure the proper line-up when saving the sheet as a text file. How can I do this (conditional formatting or macro??)

Extracting Digits From A Number With Leading Zeroes
I need Excel to create Date's of Birth from following data in column A. If the data are 7 numbers long, I need to add a '0' to the start and have used the custom format of 0#######. This works fine but I need the data in column B to just display the first 4 digits. I have used =LEFT(A1,4) to do this, but it's not picking up the 0's which I've added.

Count The Number Of Digits That Match In Each Row
Need a formula in M2 that will count the number of digits that match in each row. The digits in H:L compared to the master list in A:A119766. The count will be from 0-5 for each row. The digits must be in same column....

A Text Number With Exactly 6 Digits In The Final Outcome
I am wanting to create a VB script that will take a number (in general format) of two or three digits, convert it to text, then make sure that there are enough zeros preceeding the number to make exactly 6 digits. Examples of the initial number (before the macro is run) and the final number (after it is run): ..

Converting A Number In Digits To Its English Representation
a2 needs to contain a number (multiple of 10 10-90) and then d2 must contain a formula that gives that number in word form.

I need to do the same thing in b2 and e2, only with a number 20-99 not neccesarily ending in 0.

Calculate Sum Of Digits In A Number String
I need to calculate the sum of digits in a numeric string. The function should keep on adding the digits of each result, till the final sum is a single digit. Please refer to the following example and result expected:-

Supposed the number entered in Cell A1 is 456789

Result 1 = 4+5+6+7+8+9 = 39

Result 2 = 3+9 = 12

Final Result required = 1+2 = 3

Move 1st X Digits Of Number To End
I need a formula to extract the first two numbers and move them to the back of the number remaining. For example, the original number is 235871, the result would be 587123.

My numbers always have six digits but may at times have seven.

If Statement Based On Last Digit Or Last Two Digits In A Number
I have 4 and 5 digit numbers. For the 4 digit numbers, I want to be able to distinguish between the numbers by the last digit. For the 5 digit numbers, I want to be able to distinguish between the numbers by the last two digits.

Example: For 4 digit numbers, I would like to do something with all numbers ending in 1, 2, 3, 4, 5, 6, 7, and 8. For 5 digit numbers, I would like to do something with numbers ending in 10, 11, 12, 20, 21, etc.

The first step in being able to do this, I guess is distiguishing between 4 and 5 digit numbers, which I know can be done by the number lenght. However, the second part of looking at the last digit or the two last digits I don't know how to do.

Data Filtering Based On Last Two Digits Of Large Number
I have a very large database where I want to filter out numbers that do
not have certain two digits as their last two. For example, the column
based on which I want to filter out data contains numbers like
197301310153. I want to filter out anything that does not end in 53....

How To Remove First X Digits And Last Y Digits From A Cell
I am editing a wine database which contains a vast amount of data, one column has the wine name and sometimes the vintage year in the begining or at the end of the cell.

Sometimes the year is made of 2 digits (03, 05, ..) or 4 digits (1978, 2004, 2005, ...).

Is there a way to remove this vintage year form the string?

to make matters worse, there is often a single quote/apostrophe in front of the vintage year, which is driving me mad as 98% of the time it is one of these hidden ones that cannot be deleted using the find/replace function.

examples are like below:
De Wetshof Finesse/Lesca Cahrdonnay ‘07
De Wetshof Sauvignon Blanc ‘07
Lord Neethling Cabernet Franc 2002
Lord Neethling Pinotage ‘01
Bouchard Finlayson Tete de Cuvee Pinot Noir ‘07
Jacobsdal Pinotage 1994
Zondernaam Sauvignon Blanc 2007
2003 Tokara Red
1976 St Emilion
03 Tokara rose
Plasir de Merle Cabernet Sauvignon ‘05

I have been searching the Internet for the past 2 days without luck on how to delete the end of string vintage year.

I have had some luck with the left side, as in:
=IF(ISERROR(VALUE(LEFT(B2,SEARCH(" ",B2)-1))),B2,MID(B2,SEARCH(" ",B2)+1,LEN(B2)))
As I am not an expert with Excel, I have no idea on how to use VBA (every time I have tried even basic things, I failed) nor even sure how the above funtion works (found it on another site).

I thought I could acheive my goal in two steps, first removing the left side vintage and use this partial result with the RIGHT equivalent funtion, but it simply is not working!

Does anyone have an idea on how to help with this?

Ideally I would love to cut the vintage year, whether 2 or 4 digit, whether on right or left of cell and paste it in another cell, so to avoid manually doing it.

However, this is surely too complicated to do, so iwould settle with just deleting the vintage year and manually typing the vintage in another cell.

Remove First X Digits And Last Y Digits From A Cell
I am editing a wine database which contains a vast amount of data, one column has the wine name and sometimes the vintage year in the begining or at the end of the cell. Sometimes the year is made of 2 digits (03, 05, ..) or 4 digits (1978, 2004, 2005, ...).
Is there a way to remove this vintage year form the string?

to make matters worse, there is often a single quote/apostrophe in front of the vintage year, which is driving me mad as 98% of the time it is one of these hidden ones that cannot be deleted using the find/replace function.

examples are like below:
De Wetshof Finesse/Lesca Cahrdonnay ‘07
De Wetshof Sauvignon Blanc ‘07
Lord Neethling Cabernet Franc 2002
Lord Neethling Pinotage ‘01
Bouchard Finlayson Tete de Cuvee Pinot Noir ‘07
Jacobsdal Pinotage 1994
Zondernaam Sauvignon Blanc 2007
Tokara Red
1976 St Emilion
03 Tokara rose
Plasir de Merle Cabernet Sauvignon ‘05
DuToitskloof Pinotage/Merlot/Ruby Cabernet

I have been searching the Internet for the past 2 days without luck on how to delete the end of string vintage year.

I have had some luck with the left side, as in:
=IF(ISERROR(VALUE(LEFT(B2,SEARCH(" ",B2)-1))),B2,MID(B2,SEARCH(" ",B2)+1,LEN(B2)))

As I am not an expert with Excel, I have no idea on how to use VBA (every time I have tried even basic things, I failed) nor even sure how the above funtion works (found it on another site).

I thought I could acheive my goal in two steps, first removing the left side vintage and use this partial result with the RIGHT equivalent funtion, but it simply is not working!

Extract And Merge Number
how can i extrac & merge number to set with three digit.

on my sample image if i has number of Ditgit#1,Digit#2 and Digit#3 on "Result" title.

How to make fomular with out VB to extrac each number and Merge number 3 digit/set on "Extrac and Merge number" title.

I have a list of street addresses. I want to alphabetize them. I just want to remove all the text (usually just numbers but not always) up to and including the first space only.

Extract Even/odd Pairs From A Number
I would like to know if its possible to extract just the even/odd pairs from a number.

Exp. I have the number 123 in cell A1, and would like to extract the even/odd pair, which in this case would be 13, the cell A2. I would like to be able to do this without the use of "Isodd" or "iseven" functions.

Extract A Number And Name From Middle Of Text
I have a several hundred rows of data that are telephone line type, number and user name all concatenated. This is the way the data arrives. I need to extract the number and user name into 2 separate columns - I do not need the phone type. Is there a formula that can identify numbers within text. I can't use Text to Columns as the leading telephone line type varies and I don't want to use VBA to do the work. There are at least 10 different line types, example of some:

SPECTRUM TIPT 0740357052 LUNCH ROOM
SPECTRUM ANALOGUE 0744076642 J BROWN
EXCHANGE LINE (PSTN/PSTN) 0742031346 HAYES SUB
FAXSTREAM DUET 0742003232 NORTH SUBSTATION
M/BANK (Spectrum) 0740318578 MB TO 4429
P PHONE (TELSTRA) 0744074159 B WILLIAMS
WITHIN ANALOGUE 0784625967 I MAKIT

the following phones start witn an "N"
PRIVATE LINE (PAPL) N7035896P ROTHMANS SUBSTATION
PRIVATE LINE (VGDL) VOICE LINK N7563069P P BLACK
PRIVATE LINE 2 WIRE PREMIUM (PAPL) N70079 FITZGIBBON G
PRIVATE LINE 4 WIRE PREMIUM (PAPL) N70335 SUBS SSKLB

Can this be done or will I have to resort to code.

Extract Number From Text Cell
I have an extract from a call logging system; one of the columns is a description of the call (this column is extracted in Text format). Within some of the descriptions is a reference number which I need to extract (sadly this number isn't kept in its own field) - is there any way to determine whether a cell has numbers in it, and if so is there a way to pull them out into a seperate column? (preferably using a formula as I'm no good at VBA!)

Extract 5 Digit Number From String
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.

Extract Number From Alphanumeric String ..
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 A Row Number From A Range
I would like to know if it is possible to extract just the number of a row from a range. for instance range("c5") would give a row number of 3. Its probably simple but its evading my attmept to work it out.

VBA Function - Extract Number(s) In String
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 Highest Number In A Row To Another Cell
I have a list of names in one column and in the next column I have a list of numbers. The names and numbers go together. In anohter cell I would like to extract the highest number and the name related to it. The list changes often.

I would like to be able to put a heading saying that "name" (in anohter cell) made the highest score and the name is entered into a cell automatically.

Extract The Total Number Received For The Various Scenarios
I have a spreadsheet with 4 columns named:

type

Is it possible to use vbscript and extract the total number received for the various scenarios:

numbers received on specific date before specific time

Extract Number From Alpha-numeric Data -
Aguirre: The Wrath of God 552
Ai No Corrida 619
Aileen Wuornos: The Selling of a Serial Killer 821
Airplane! 671
Akira 764
Albero degli Zoccoli, L' 641

[etc]

I want to extract the page numbers (the final numbers) and place them into a new, separate column.

I am using Microsoft Excel X for Max Service Release 1.

I am competent on basic excel but not familiar with complicated formula etc.

Formula To Lookup The Numbers From Above And Only Extract A Number Once
I have a spreadsheet on a weekly data with diferent reference names
e.g.
101
101
101
105
105
101
101
109
109
114
114
101
101

what I want is a formula to lookup the numbers from above and only extract a number once
e.g
101
105
109
114

Extract Top Row Number After Filter
I have to find out the top first number aftet filtering the data based on particular column. ie each time when i am taking each filter value the row will start from different number.

so how to find the top row number after filter? U got what i mean?

Extract 7-digit Number From A Mixed String
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 Text & Number From Cell
How can i extract text and number from a cell .For example I have a series of data like 453x, 45y in Column "A". I want text in column "B" and numbers in "C" column. The cell has variable data with diffrent length.

Extract Number In Column + Delete The Rest
I have the following which is in a column.

Extract The First 5 Characters In A String, And Convert Them To A Number
Say I have a string "09800EBHR052708"

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

Extract X Number Of Characters Followed By Specific Character
How can I extract mid section of the string which is always 5 characters long and is always followed by a period "." ?

My account strings are in 3 sections (but the third section isn't always used)

For example,
1.10210
1.22556.001
900101.56201
955261.54444.001
5566625.58886.957756

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?

example:
test123test128test131
test124test129test132
test125test130test133
test126test131test134
test127test132test135
test128test133test136

result: ....

Pull/Extract Number Before Specific Character In Cells
What formula or function truncates a text field that is in the following consistent format:

number. name
ie. 3. Super Bella
and 150. Taoist

I wish to truncate the field so I have just the integer without the dot, space or word(s).

Tried Len, Left & Replace but they require a specific number of characters. Since the number can be 1 to 3 digits long, this doesn't work.

Extract Negative & Positive Number From String
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 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.

Extract Number From Number
One column has numbers and text. e.g, cell B4 contains 122 E4. I wish the numbers before the text to be placed in the adjoining cell in the previous column. So the above would now see A4 containg 122 and B4 containing E4. What formula do I need in order to extract the three numbers which begin in each cell and place them in the previous column? I attach small s/s to illustrate.

Extract Fraction From Text Without Converting To Date Serial Number
I have a cell with the following text in it that is being imported from a website "5/1 Smith T win". What i want to do is extract the "5/1" part without it being in the format of a date. I want it to be extracted in the format "?/???". I have entered the following formula to extract the "5/1"

Countif (extract The Number Of Times A Name Appears On A Certain Range Of Cells On A Worksheet)
I am trying to extract the number of times a name appears on a certain range of cells on a worksheet, now I can get this to work, but I am sure there is a better way I just cant get it to work!

=SUM(COUNTIF(sales1!\$B\$8:\$F\$11,A18),COUNTIF(sales2!\$B\$8:\$F\$11,A18),COUNTIF(sales!\$A\$8:\$B\$11,A18))

I have only used 3 pf the worksheets, but in time there could be up to 30.

Extract Character From Date Format & Number Of Days Between Date
I have two questions regarding date format and hope you can provide input.

1) say 01/01/2010 displays as Jan-10 and i need a new column to state it as JAN. What function should i use to achieve it?

2) i need to state the difference between first day and last day of the month. What function should i use to achieve it?

How To Give Last 6 Digits Only If A #
I have a formula now that is =right(C2,5)+0 that is working well. However the data has grown and sometimes there is also 6 digits now instead of 5. So I need it to pick up either one 5 or 6. When I change the formula to 6 it works but picks up a / which happens to be before the 5 digit # sequence when there is only 5 digits. It works great for the 6. Is there another way around this so I only get the numbre digits if there are 5 or 6 and not the /. Maybe an if statement. I've tried several ways but none work right. The only other thing I can think of is to get it as above with the =right(C2,6)+0 and then afterwards to a find and replace and remove the / from the data. I was just tryign not to add an extra step to the process. Any ideas please?

Example of the data in coloumn C2 is:
15/2000/4567/NA/NA/97305or with 6 digits at the end15/2000/4567/NA/NA/973052there is always just 5 or 6 digits at the end that I need.

Filter Last Two Digits
I tried every filter function I know of, to no avail, and am yielding my stupidity to the forum. I have this series of numbers as an example:

456912
789547
785171
658712
968712
369874
258741
127812

All I want to filter is all values ending with 12.