Extracting Numbers From Alphanumeric Strings (unite The X Amount Of Numbers)

Aug 16, 2008

Is there a way either by VBA or manually (preferably both, if possible) to actually unite the X amount of numbers that are in a cell given the contents is alphanumeric? I'll give you the following examples to see if you can understand what I' referring to?

DATA output should be
asd67,h876 --------> 67876
2,3,ujdj5&34 -------> 23534
909k86m34 --------> 9098634

View 14 Replies


Extracting Numbers From Alphanumeric Strings (strip Numbers?)

Aug 15, 2008

I import data from another program in order to evaluate it. Unfortunately, one of the fields I need contains copyright data, however, it has been very inconsistently entered into the database. For example, sometimes the data appears "c1999." or "-1999" or "" or "[1999]" or even "19?" and also sometimes "1999, 1990" and many other variations on that. I discovered the link in the excel help file about extracting numbers from alphanumeric strings, but my situation is still too variable for it to apply; that file didn't take into account that alphanumeric strings don't always lump numbers and letters together. I was able to correct a few things, but my command of excel isn't knowledgeable enough to really come up with something effective.

Some ideas I had that I don't know how to implement: is there a way to strip non-numerical characters from an alphanumeric string? (I've been doing some find/replaces to get rid of some of it, but that is obviously not very efficient when I have to repeat this process daily.) Perhaps then I could just detect the first 4 numbers of the string somehow. However, that doesn't solve the problem of when a wild card is used as in "199?" or "20?" etc.

Bottom line, I just need to grab the first four numbers that appear in the string (but NOT additional numbers that occur after a wild card or a space if the year was not completed in 4 numbers; in that case I'd just be happy with a null value).

I've been doing this with a formula so far. My only experience with macros has been in simply recording them, not actually writing them, but I'll give anything a try.

View 9 Replies View Related

Extract Numbers From Alphanumeric Strings

Mar 7, 2009

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

Conditional Extraction Of Numbers In Alphanumeric Strings

Apr 6, 2008

I have a column with product packaging sizes with different syntaxes, such as:

"2 x 5 kg"
"200 l (212 kg)"
"1000 l (1400 kg)"
"5 kg"
"20 l (20,54 kg)"

As you can see the syntax varies a lot! Now I need a formula to extract certain numbers for different calculations. E.g. if the string has a letter "x" then the number(s) in front of it would be extracted. Or if the string has the letter "l" or "kg" the numbers in front of them (including decimals) would be extracted while discarding the rest. Is this even possible? Personally, I would change the whole system and break the strings across a number of columns but unfortunately it is not up to me...

View 4 Replies View Related

Extracting Letters And Numbers In A Random Alphanumeric String

Dec 29, 2009

I've got this problem: I need to separate around 40 alphanumerical entry in Column'A' to Columns'B','C','D','E'..

View 6 Replies View Related

Separating Or Extracting Numbers Between Two Strings Into Two Columns

Mar 13, 2014

As enclosed in my workbook,I want to separate the numbers between two strings of which one is in the left hand side and other is in mid.The data is in Column D.The simple way to understand is that,

Total Amount = Amount X Exchange Rate

Total Amount is in Column B, where as "Amount X Exchange Rate" is in combined form in column D.Yes, this I want to separate i.e Amount separately column and Exchange Rate in separate column.I have shown one expected result in column E,F.There are certain characters like ],= which you have to not to take any amount after ] or =.The currency is somewhere,$/$$/INR/IRS,etc.

View 4 Replies View Related

Delete The Formula In The Amount The Unite Price Comes #value

Jan 18, 2010

(To keep things simple from left to right Column A-H)

The Amount Column seems to be my problem, it has the formula =H98*B98 just a simple multiplication formula to get my unit price x my qty. When I delete the formula in the amount the unite price #value! error goes away. and all that is in the other error box is =IF(P98>0," per piece","") it just puts "per piece" in the box when something is typed.

I have a vlookup formula in Column F (thank you VoG)

To pull prices from another worksheet.

View 9 Replies View Related

Extracting Numbers From Text And Turning The Numbers Into Rows Of Data

Feb 23, 2010

The format of the text in which I need to extract numbers is as follows:

23411268 - 23411270

Need to extract the following:


These numbers have to be listed in three seperate rows.

View 14 Replies View Related

Extracting Numbers :: Pull Numbers From Another Column

Oct 24, 2007

I'm trying to pull some numbers from another column. I want to pull the numbers that have an X separating them like 7X125, 48X192, and 27X90.


FA, VF-2000-3-7X125-18-A, AFS
FA, VF-2350-48X192-6-RGB, FC
FA, VF-2020-27X90-18-A,RFI, FEX, ACP, 2IT

View 9 Replies View Related

Take Out Set Of Numbers From Alphanumeric Cells

Nov 20, 2012

I have about 200 cells with 2 different format in text and numbers but they all have a number in between right after /vgh/.

Is there a formula I can use that will return only thee set of numbers removing the text before and after it.

Below are the 2 different examples.

Example 1 : h4ffkwd1-5o/vgh/2447044/eml-anet_wlcm-h-visit/?hs=false&tok=3GMdjcBsfbfBk1
Example 2 : h38ahlur-19/vgh/163311/?hs=false&tok=1CIRLlqwLSB5g1

I have these sort of text and just need the middle set of numbers.

View 6 Replies View Related

Non-zero Numbers From An Alphanumeric String

Jan 9, 2007

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

Remove Numbers From Alphanumeric Cells

Sep 17, 2008

I have about 10000 cells of varying length in text and numbers but they all end with numbers on the ends of them. Using text to columns doesn't work because they're all different lengths and it cuts them off in the wrong place.

Is there a formula I can use that will return only the text from these cells and remove the numbers. It would definitely save me time from individually deleting the numbers at the end.

View 11 Replies View Related

Remove Numbers From Alphanumeric String?

Jan 11, 2013

I have a list (SIC Codes) and I want to remove the numbers. The numbers range from 2 to 8 deep. The list exists in column B and I want the new list in column C.

01 Agricultural Production Crops
011 Cash Grains
0111 Wheat
0112 Rice
0115 Corn
0116 Soybeans
0119 Cash grains, nec
011901 Pea and bean farms (legumes)
01190101 Bean (dry field and seed) farm
01190102 Cowpea farm
01190103 Lentil farm
01190104 Mustard seed farm

View 3 Replies View Related

Remove Numbers From Alphanumeric Cell

Oct 23, 2007

In cell A1 i have the value ABC123.

I want to remove the 123.

Can you tell me how to do this?

View 14 Replies View Related

Sorting Alphanumeric Part Numbers

Jan 7, 2007

I have a fairly large list (1200 rows) of part numbers that I would like to sort. The part number has text and numbers, with a number in the middle. I need excel to ignore this middle number when sorting (but not any of the other numbers). Excel currently sorts like this:

MKDSN 1,5/ 3
MKDSN 1,5/ 3-5,08
MKDSN 1,5/ 4
MKDSN 1,5/14-5,08

when I want it to sort like this:

MKDSN 1,5/ 3
MKDSN 1,5/ 4
MKDSN 1,5/ 3-5,08
MKDSN 1,5/14-5,08

The first few letters in the part number or the numbers at the end aren't consistant. But it's always the numbers after the "/" and before the "-" (where there is one) that I want to ignore.

At the very least I would I need the list in alphabetical order. I don't need the list to be sorted by the "ignored number" at all. Meaning, I would be happy with this result:

MKDSN 1,5/ 3
MKDSN 1,5/ 4
MKDSN 1,5/ 2
MKDSN 1,5/14-5,08
MKDSN 1,5/ 3-5,08
MKKDS 2/24
MKKDS 2/20-3,5
MKKDS 2/ 2-3,5

I don't mind getting rid of "/" or spaces or "," in the part number, but I would prefer to not split the part number between two columns.

I tried creating a custom list, but there are just too many varieties to list them all.

View 8 Replies View Related

Return The First Two Numbers Of An Alphanumeric String

Dec 4, 2008

Anyone know how to do this?

View 9 Replies View Related

Remove Numbers From Alphanumeric String

Aug 23, 2008

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

Sort AlphaNumeric Text By Numbers

Dec 6, 2006

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

Pull Numbers From Alphanumeric Text

Aug 30, 2007

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

Remove Numbers And Symbols From Alphanumeric Cells

Mar 30, 2009

I am looking to remove numbers and symbols from a single cell.

For example, I start with something like:
Cell A1: M. Flank1, T.D. Chang1*, T. Person2, J. Doe3
Cell A2: T.R. Money1*, Y.R. You2
and I want:
Cell B1: M. Flank, T.D. Chang, T. Person, J. Doe
Cell B2: T.R. Money, Y.R. You

And, there are about 300 more, with varying number of people per cell.
I'm not great with Macros, so if there is a way without macros, that would be good, but, if a macro works best, I can give it a shot..

View 5 Replies View Related

Cross Check A List Of Alphanumeric Numbers?

Jun 24, 2007

I posted my question on another site and didn't get very many responses. I've got a list of alphanumeric numbers that I'm trying to cross check against a bingo grid.

The bingo cards are in a PDF format, so here's a screenshot. There's a separate sheet for Red; White; & Blue.....

View 9 Replies View Related

If Alphanumeric Text Equals Certain Criteria Count Numbers

Sep 30, 2009

On my spreadsheet I have multiple rows and cells containing alphanumeric text (text is a combination of a person's name and goals scored) i.e. Holland1 appears in row 3 cell P3 then Holland3 appears in row 6 cell O6.

What I would like to be able to do is have a forumla that looks at every row and cell in my range and sum the numbers of the alphnumeric text if the cell contains a certain name i.e Holland.

I had tried to use the array formula =1*MID(A1,MATCH(TRUE,ISNUMBER(1*MID(A1,ROW($1:$26),1)),0),COUNT(1*MID(A1,ROW($1:$26),1))) which I found on the internet but this only works for one cell and doesn't allow for a set criteria to be met.

View 8 Replies View Related

Comparing 2 Columns With Numbers / Letters And Alphanumeric Values

May 13, 2014

I have 2 columns i want to find out which items match in each column and put the matching value in column c. I have tried Vlookup and continue to get an N/A .. I tried countif and I get either an N/A or a value error. I have tried turning the cells into text but that is not working either..

column A has about 1700 rows and column B has about 4000

MOST CELLS ARE 6-7 VARIABLES.. satrting with either 01, 02, 03 with 4-5 letters following or have a 6 digit number or 6 letter value.

View 1 Replies View Related

Macro To Iterate Alphanumeric Serial Numbers From AAAA001 To ZZZZ999

Oct 3, 2012

i Am trying to get this project underway and can attempt this manually in excel but will take ages. I was wondering if there is a code I can use. I am trying to get a 7 character alphanumeric serial number for units as a 4(alpha) and 3 numeric number. like say AAAA001 , i want to list on a row all the numbers on a sheet right upto ZZZZ999

View 5 Replies View Related

Combining Strings Of Numbers And Ordering Them

Jan 26, 2007

I am trying to come up with a formula which would combine two groups of numbers, put them in numberial order and remove duplicate numbers:

3 6 10 22
1 3 5 9 10 13 19

The above two groups of numbers are in separate cells in two different rows.

the result would also be in a different row: 1 3 5 6 9 10 13 19 22

View 14 Replies View Related

Reading Numbers As Strings From .csv File

Jan 7, 2010

I have a .csv file that contains numerical data in the form of strings, much of which starts with leading zeros (i.e. "012345"). I double checked to make sure the .csv file has the leading zeros and that they are wrapped in quotation marks in notepad, and it does. However, when Excel opens the file, it must read those strings as numbers, becuase it chops off the zeros. Does anyone know of way to fix this? Can Excel read numbers as strings straight from a .csv file?

View 3 Replies View Related

Find Numbers In Text Strings

Nov 21, 2006

I have several cells in a column that look something like this:

Cell A1: abc 1234 def ghi
Cell A2: xxxx aa b 245 qqqqq
Cell A3: abcdefg hij kl mnopqr s

Is there an excel formula or combination of formulas I can use to identify:
(1) whether any given text string (such as those above) include numbers, and
(2) what the first number (which could contain 1-4 digits) contained in the text string is?

View 3 Replies View Related

Extracting Numbers

Feb 4, 2006

i need a formula to extract numbers.

if cell a1 is "3cash 5stock", i want to extract the number "3" to cell a2
and extract the number "5" to cell a3. the number in cash stock can vary
between 1 to 8 and vice versa.

View 14 Replies View Related

Copyrights 2005-15 www.BigResource.com, All rights reserved