# Extract Numbers Based On First 2 Digits

May 27, 2008I 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.

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.

I am trying to filter my excel sheet based on certain digits in a column of part numbers. The part number has 10 characters. I would like to filter it so that all part numbers where the 4th character is the number 5 or 7 is listed and where the 5th character is a zero. (i.e. R4X5831310 is a part number where 5 is the 4th character; I would like the filter to show this part number)

View 2 Replies View RelatedI have the following working great, but would like to see it refine a little, as the data vlookup is 6 digits, but i only needs the last 4 digits is enough for me to work, my question is how do i go about adding that to the following function i have implemented and working fine.

=IF(ISERROR(VLOOKUP(B4,' cmfs01home$peter[tracker data 4-25-14-a.xlsx]ControlSheet'!$B$2:$F$301,4,FALSE)),"",VLOOKUP(B4,' cmfs01home$peter[tracker data 4-25-14-a.xlsx]ControlSheet'!$B$2:$F$301,4,FALSE)

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.

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.

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

I have a long string that has a list of digits in the middle that I want to extract. The string is variable length and the number of digits I want to extract is 5-7. I also have slashes in between the numbers I want to extract. I need a UDF that allows me to extract the 5-7 digit number from the string and restrict around the slashes (i.e. if two sets of digits in the string match the condition for extraction, extract the one around the slashes.) For example my original data is like

1. aaa/12345/aaa/123

2. aaaaa/123456/aaaaaa/3423

3. 323/aa/1234567/aa

and I want for results

1. 12345

2. 123456

3. 1234567

Is there a UDF that allows me to do this?

I have in my cell a number, namely, 5260007005020024100055040300004110000000

What I would like to do is extract a set of digits from it,

Starting from the second 2, and shown here in the dots. I need all of the numbers in a separate cell, "52600070050200 .24100055.. 040300004110000000"

Hope this is clear. bearing in mind the number will remain as one so I would need to start at 14 then using LEN or something I'm not sure, extract the following 8 into another cell.

I basically I have a column with numbers. All the numbers are positive integers. What I like to do is have a VBA function that extracts the integers with the largest number of digits. So for example if we have the following column:

12

123

234

12346

2345

[code].....

So basically we search for largest number of digits, and extract the numbers that fit this category, which could be just one number or multiple numbers.

I have a excel file, I need to remove the first two digits if they are certain numbers, such as 12. For example, if the number is 12987654, then I need remove 12, and it will be "987654" , but if it is not 12 in the first two digits, then keep it no change, for example if it is 345678, then keep it.

I barely work with Excel formulas, now I need connect the excel file with my Database table. I need to make the file matches the DB.

Example numbers:

21130 & 21065

I want to check each number if EITHER of the two conditions is true:

1. if the third digit from the right (the hundreth place) is greater than zero;

or

2. if the second digit from the right (the tens place) is >=6.

If either is true I want to add a particular number to the original number.

My example numbers meet questions 1 & 2, respectively.

this may be simple but I need to convent say

A1 = 09

B1 = 23

into

C1 = 0

D1 = 9

E1 = 2

F1 = 3

..etc

I have a unique 6 digit number in a column. I have 2500 rows. I need to cut the first three numbers only leaving the last 3 numbers in the column for each row.

Example:

112345

234325

I would need 112, and 234 deleted. Checked the forum and cannot get quite what I want.

I have a column with numbers in about 500 rows. The entries are 5 numbers long and others 8. So I thought i could use one of the following: A macro code to tell a cell to delete the first 3 numbers if the entry is 8 numbers long?

OR

A macro code to tell a cell to reduce itself to 5 digits long starting from the right? Attached is a small example

I need to list 4 digit numbers in each section followed by commas, but whatever I do it goes to 3digits (e.g. I need "1234,5678,9123" and as soon as I hit Enter it goes to "123,456,789,123"). It wont work to format as text because I have a whole bunch of 12 digit numbers to break up into 4.

View 9 Replies View RelatedI need a way to display all 8 digit numbers that have the digits 1-8 in them. (ie. 12345678 but NOT 12345679 OR 12345677) Also If I could somehow divide by 13 then check for whole numbers

View 14 Replies View RelatedI am inserting data into my spreadsheet using VBA code to read a file and insert the data into the relevant cells. My problem arises when I have a string such as 80830410205724044. The actual code that inserts the data is as shown (temp is dimmed as string)....

View 9 Replies View RelatedI have a UPC list. Some are more than 12 digits, and some with less than 12 digits. I need to make sure there are 12 digits in each UPC. I know how to count using LEN, strip leading zeros of those UPCs that are >12 digits using RIGHT.

What I need now is any number with less than 12 digits, such as 000123, add a 4 to the beginning (4000123) and fill in '0's in between the 4 and the short UPC number to make 12 digits, 400000000123. They vary from 1 to 13 digits.

I have some numbers around 200 numbers which are not same digits i.e. if 1st number is 7 digits, 2nd number is of 10 digits.

Like this even if i have a number of 1 or 2 digits then all number should have equal digits i.e. in 200 numbers if one numbers is of 13 digits then all the numbers should be 13 digits.

To do this first i am doing this to the right align & comparison all numbers & taking the highest digit number & pre-fixing the other numbers with zeros so that all number are equal in number of digits. This is taking huge time.

I want to make a macro or any easy method so that all should have equal no of digits.

I want that if 200 numbers are pasted in the column E i.e. from E2 to E201 then all should be converted to equal digits number by prefixing zeros & should appear in column G from G2 to G201.

And after converting them it should be prefixed with D/ OR D/ABC:

This will be present in B2 cell i.e. this cell will be blank always. And once I have any thing in that cell that should be prefixed to the converted numbers in the column G & they should appear in Column I.

And again the numbers in column G should be prefixed with the content in B2 & should appear in column K in the Ascending order sorted.

Suppose if , I have 500 numbers then they should be converted automatically to equal digits & appear in Column G & prefixed with the contented in cell B2 should appear in Column I & sorted series in ascending order with prefixed with cell B2 in the column K.

And if I have 15 numbers only then it should do the same job easily. Here the numbers of numbers are not defined.

All this should be get done on click of the command button.

I have a macro that copies long decimals (11 places) from an Excel file to a CSV file. However, the long decimals often get truncated from 11 places down to 3 or 4. I've tried a number of different coding methods to combat this, the most recent and most successful of which is shown below

Application.Workbooks.Open Filename:="C:...2008_alldata.csv"

Cells.Select

Selection.ClearContents

ActiveWindow.ActivateNext

Application.Goto Reference:="AllDataTable"

Selection.Copy

ActiveWindow.ActivateNext

Application.Goto Reference:="R1C1"

Selection.PasteSpecial Paste:=xlPasteValues

Range("R2:BI5000").Select

Selection.NumberFormat = "#,##0.00000000000"

ActiveWorkbook.Save

ActiveWindow.Close savechanges:=False

However, sometimes this method also fails. Is there a better way anyone knows of to ensure that the entire decimal is copied?

vb code that will break out each 6 digit media used in their own rows such as in the illustration below.

Sample data before macro

Master Backup

GTI - Hostname

[Code]....

I have a column that has section numbers like 001.002.006.003.010.011.002.

I would like to divide that single column into seven columns with only the single or double digit in it, ie

1 in a cell

2 in a cell

6 in a cell

3 in a cell

10 in a cell

11 in a cell

2 in a cell

Have been using MID and FIND togther, but when I get to the double digits like 10 an 11 I run into problems.

I have a text string in cell A1 as below:

repairs booked in Dec,11 (INR 37k)

training fees Dec,11 (INR 42k)test Fee Reimb for 03 emp Dec,11 (INR 56k)

skill fees booked in Jan,12 INR 52k

Reimb for 01 employee in Jan,12 INR 8k fee accounted- xyz INR 250k, Quick solutions INR 52K, ABCD India INR 272K, Layer Technologies INR30K and complex mgt INR 21K

Note: (INR 37K) means negative number i.e. -37

Now my task is to manually total the above amounts in calculator, i.e.

-37-42-56+52+8+250+52+272+30+21 = =550

like this there are 100s of cells, could derive a formula for this task.

I need a formula to drag down the attached that will place zeros infront of any numbers that don't have 9 digits. so if a part number consists of 6 digits then i need 3 zeros in front of it, if a part number consists of 9 digits then i dont need any zeros proceeding it.

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

how to sort based on the first two digit..

I need to sort the COMM CODE ( which is at Column no 3 )

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.

I am trying to write a bit of code to go into a macro and as I am very much a leaner, I thought I would create the formula I need and record it, then copy in the relevant bit. However, I cannot even get the formula to work - never mind the macro bit!! The formula I am trying to do is something like this...

In column S:S

IF the first digit in R2 (and eventually R2 to R lastrow) = 1 to 3, concatenate Q2 (and eventually Q2 to Q lastrow) + "/" + "Q1"

IF the first digit in R2 (and eventually R2 to R lastrow) = 4 to 6, concatenate Q2 + "/" + "Q2"

IF the first digit in R2 (and eventually R2 to R lastrow) = 7 to 9, concatenate Q2 + "/" + "Q3"

IF the first digit in R2 (and eventually R2 to R lastrow) = 10 to 12, concatenate Q2 + "/" + "Q4"

Column R:R has the year and the first digit of Q:Q hold the month so I want to end up with 2007/Q1 etc.

I could really use some excel function. Within my office, we work with several hundred files. Each employee is assigned files based on the last two digits of the file number. What I need is a way to identify what file is assigned to which employee based on the term digits of the file.

So for example, I have the following list of files:

1002856101

22781721

1044863815

1008799064

1044779765

1006511115

1007641804

0729939256

5303486020

8364709

0014094759

0019921519

8172717

I'm able to do a formula to get the term digits (meaning the last two numbers), but i'd like to have another column that can put names based on the term digit column. For example, Tom might work 00-04, Sally works 05-09, Greg works 10-15, Lucy works 16-21.. etc

I came across the below IF formula that is exactly what i need, except it only works for two associates and not the multiple that i need.. but it looks to be a good starting point nonetheless.

------------------------------

=IF(C2<50,"Sheryl","Lisa"). You should enclose Sheryl and Lisa with quotation marks as these are string values.

You can also use (if A2 is where the Loan # is):

=IF(Right(A2,2)*1<50,"Sheryl","Lisa")

The formula will acquire the last 2 digits of the loan and check it if it's for Sheryl's or Lisa's.

-----------------------------

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

I need to enter a revision string into a file.

Basically, I need to combine the filename and its generation time and then encode it into a string (i.e. a 6 digits HEX code or 6 digits using [0-9][a-z][A-Z]) so it would generate a pretty much unique revision number.

So, is there any function that you can think of that would do something similar?

If not, what is the best to tackle this?

Generate a CRC or md5sum?

What would be in your opinion a quick and efficient way to solve this tricky one?

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

1999 Tradition Juracon 375ml

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!

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.

just got a problem with this guys, sample :

in A1 i copy and paste -> "previous / 12.25 " or " "previous/12.25"

and i want in b1 to only have "12.25".

CAn a formula/macro be provided to extract the numbers (including Decimal) from a cell value containing alphanumbers?

For eg.Down 3,492.00 INR should be extracted as -3492.00

Up3,492.00 INR as 3492.00

Please note that the numbers may be of any digit. If it contains down, then the number should be negative and if UP then positive.

I'm trying to obtain a daily/monthly sales total. As you can see from the sample I've left, I have a number of different sales dep't and have to tally each one, but I have a situation where 1 of the dep't I need to keep a tally (including text) of what the amount refers to (but all in the same column, can't seperate them into different columns.......just in case of an doubts). What I need to accomplish is a formula for the following:

1- that it can recognize AND sum across the values. (TOTALS column)

2- that it can recognize AND sum down the SALES D column.

Sheet1 *ABCDEFG1DATE SALES A SALES BSALES CSALES DSALES ETOTALS22/26/2009$458.00 $23.00 $- * $20.00 Late fee + $30.00 purchase$9.00 = $540.0032/27/2009$875.00 $- * $56.00 $12.00 late fee $100.00 delinquency$43.00 = $1,086.0042/28/2009$1,235.00 $12.00 $42.00 $7.00 vis $16.00 mcd $23.00 amx$13.00 = $1,348.005SUBTOTALS$2,568.00 $35.00 $98.00 $208.00 $65.00 $2,974.00 Excel tables to the web >> Excel Jeanie HTML 4

I have got a list of numeric abbreviations, for instance 10739011/21/31/41. What it should really display are the numbers 10739011, 10739021, 10739031 and 10739041 (the first six figures stay the same). All the numbers in my list are 8 figures long. I want to change the list from the list seperated by the backward slash to the complete numbers. I have uploaded an example of the list with backward slash between the numbers. Is there a way that Excel can automatically change these numbers to the full numbers?

Because all the numbers are 8 figures long, I thought the first 6 figures of the 1st number can be copied and those 6 figures pasted before the other two figures after the backslash. Auto Merged Post Until 24 Hrs Passes;sorry, pressed OK too quickly. The problem is that there are sometimes 4 numbers in the cell, sometimes 6 and once three. I would like Excel to complete all the numbers in the cell and then move on to the cell underneath it and so on. Also, I would like each number to have it's own cell.

I want to EXTRACT LAST 4 numbers from a sentence

EX:

A1:what is your name 1234

To be

B1:1234

I have a data set that I imported from Access. One of the columns contains the code for specific work activities, for example 13Z or 9A. I need to extract the numbers only from the cells in that column so that they are in separate cells in a separate column. I've been trying to use left, right, or mid functions, as well as text to columns with varying degrees of success.

View 2 Replies View RelatedI need some code that extracts everything after the last number in a cell. For example, in A1 I have

Flat 3 45 New Road

and need to rearrange it to get

Flat 3 45

into another cell. I've found this code;

I require a formula to extract minutes and seconds from the below cells and place them in the cells beneath the required data heading as shown.

Code:

ABCD1

REQUIREDREQUIREDREQUIRED2

DATA DATA DATA3DATAMINUTESSECONDS 1SECONDS 246m02.4s62453m58.s358061m11.98s1119870m58.54s05854812m58.04s1258491m12.56s11256105m08.s580115m.01s501121m00.17s1017

ADVERTISEMENT