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


ADVERTISEMENT

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

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.

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

Sum Values From Alphanumeric Strings

Feb 8, 2007

On a work sheet I have 20 columns that may or may not have data in it. In the 21 column I want to have a sum "of the values" in the other columns. Problem is the data is in this form i.e. 6 01/17/07 smr or 15 1/19/07 gtw or 24 01/21/07.

To clarify each cell will have a value of a number from 1 to 99 followed by 2 spaces. Then it can have 2 more spaces and an id tag or just finish with the date.

So, from the example above in column 21 I would have a current value of 45 and it could change if more values were added.

Note values are generated from a macro. It may be possible to change the macro so that a comma or such may be used to seperate the different "values / data types"

View 4 Replies View Related

Finding The Highest Value In Alphanumeric Strings

Oct 28, 2009

I have a column of one to three digit numbers (I5:I34), some of which contain an asterisk after them. In I35 i want a formula to find the highest value from that column. Additionally, if two numbers in the column are the same value, but one contains an asterisk after it, it should use that value in I35, instead of the one without the asterisk.

View 6 Replies View Related

Split Alphanumeric Strings Into Three Different Columns

Feb 27, 2014

I am working on a report but I need to split of text cells that contain text and numbers. I had worked in some formulas and I got stuck again when I drag down the formulas. In the attached file, you will see two tables. The one in the left is my current job. In the table of the right is how the table must look like.

The table is complete and will give you the whole idea.

Basically, I need to split the cell into three categories that are "Family", "Model" and "Phase". In example:

Cell Family Model Phase
CCA CCA
DC50X DC50X
DCX3300CRDDCX3300CRD
DPC2434 DPC2434
DCT-1700DCT1700
DCT-1800DCT1800
DCT-1800P3DCT1800P3
DCT-1800P4DCT1800P4

Attached File : Family Model.xlsx‎

View 4 Replies View Related

Change Strings Data Alphanumeric

Jan 25, 2010

I have in two cells :
a1 b1
1m 1-3-6-11-17

how can I get:

1m1
1m3
1m6
1m11
1m17

using vba code.

View 8 Replies View Related

Stripping Text From AlphaNumeric Strings

Aug 29, 2006

I'm working on an attendance spreadsheet that shows the employee's absence using the format "X####", where X can be one of the following three values: S(for sick time), P(for personal time), and V(for vacation); "####" is the number of hours absent (can be from 0.25 to 8.0,measured in 15-minute increments, # can be either a digit from 0 to 9 or a decimal point, and there can be from 1 to 4 characters, but must be >0(I'm not worried about data validation yet).Examples are P8.0, V.5 (V0.5), S2.75,etc.

The spreadsheet is laid out such that each row represents an employee, and each column is a workday. After the employee's information are three columns labeled P,V, S. I'm trying to generate a formula that when applied to an employee's row, will display a running total of the absent hours based upon personal, vacation, or sick time taken. We'll call the range "CAL"(Short for calender)

To strip out the numeric characters on the right, I use the formula RIGHT(CAL, (LEN(CAL)-1)). To Determine which column the data should be summed in, I use the formula LEFT(CAL,1). Blank Cells are permitted, in fact most of the row will be blank. Now that I've figured out how to strip out the alpha from the numeric, how can I get the totals? I apologize for writing "War and Peace," but I wanted to be as concise as possible-

View 5 Replies View Related

Summing Numeric Parts Of Alphanumeric Strings

Nov 17, 2009

I need to sum the numeric portions of any cell containing a certain letter within a row. I found a solution that works if all the cells within my row are either blank or contain a string with the "desired letter" lets say the letter is "a" so that we can compare it to ....

View 6 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:
123
0123
01(2)3
0.123
123abc
0123abc
01(2)3abc
0.123abc
a1b2c3
1a2b3c

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:

=1*MID(A7,MATCH(FALSE,ISERROR(1/MID(A7,ROW(1:100),1)),0),20)

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

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

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

Conditional Concatenation Of Text Strings

Jul 17, 2007

I want to concatenate the text of several cells into one cell that satisfies some condition (simular to a SUMIF). For example:

1 AB
2 CD
3 DE

The formula I'm looking for should return ABDE in case of 1 and CD in case of 2.

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







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