Extract Last Letter And Remaining Characters From Alphanumeric String

May 14, 2013

Having trouble with this one. Searches seem to bring up every other variation of extracting info from strings except this.

I have cells which contain alphanumeric strings as below and, using a formula, I want to extract everything from and including the last letter to give A 2-3, B 3 and C 3-4 in the examples below.

Sub-base A 2-3
Sub-base B 3
Paving C 3-4

View 7 Replies


ADVERTISEMENT

Alphanumeric Characters Extract Only The Numerics From Cell

Jun 12, 2008

I have the following in Column A

28VNC
JR2KL
29C9O
PQS11

I'd like have this result

28
2
299
11

How would I achieve this.

View 6 Replies View Related

Remove Non-alphanumeric Characters From A String

Aug 2, 2006

I am trying to remove all characters that aren't letters or numbers from a string. Is there any way to differentiate between a non-alphanumeric characters and alphanumeric characters? I'm thinking of something like "ISTEXT()" that I could use on one character at a time. Or are there any wildcards I could use in the Replace function?

View 9 Replies View Related

Remove Non-alphanumeric Characters From A String

Aug 2, 2006

I am trying to remove all characters that aren't letters or numbers from a string. Is there any way to differentiate between a non-alphanumeric characters and alphanumeric characters? I'm thinking of something like "ISTEXT()" that I could use on one character at a time. Or are there any wildcards I could use in the Replace function?

View 9 Replies View Related

Substitution Of Specific Characters In Alphanumeric String

Jun 13, 2013

I have a large column of text with multiple entries similar to this: PC3L-10600R-9-11-A0. I need to replace the "11" with a 13. However I have other instances where the 11 appears (PC3-12800E-11-11-D0 or PC3L-12800R-11-11-A0).

I have found that I can use SUBSTITUTION

{=SUBSTITUTE(A50,H50,I50,1) and =SUBSTITUTE(A61,H61,I61,2)}

to handle the specific instances but I'd like to have a way to combine this type of function logically in one command.

View 9 Replies View Related

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

View 8 Replies View Related

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

View 5 Replies View Related

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

View 9 Replies View Related

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.

View 4 Replies View Related

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

View 11 Replies View Related

Extract TEXT From Mid Of Alphanumeric String Using Formula

Jul 18, 2012

I have another question regarding extracting Alphabet from the alpha Numeric String:

Column A

POT470G12B
PBY100G12
PCLN6400S12
PCX.US10X12
AAPL6000G12A

BP380S12A
BP390S12

The output i want from the string is :

Column A
Column B

POT470G12B
G

PBY100G12
G

[Code] ........

View 9 Replies View Related

Extract String Between Characters After Specific String

Dec 22, 2013

I have the following type of info in A1,A2,A3...

Code:
nameGaryage40cityPittsburgheight190
age30height150
ameLindacityMichigan
citySan Jose
ameHarryheight180age50

My goal is to get as close as possible to this,so it will be easier to sort and manage

Code:
nameGaryage40cityPittsburgheight190
nameLindaage30cityMichiganheight150
nameHarryage50citySan Joseheight180

I can't use the "" sign as delimiter to separate them into different columns because the age,city,name and height fields are in random positions on different cells.The good thing is person's name will always come after "name" string, age is alwals followed by "age" string, so it cannot be like nameheight40Michigan180

I think the following would be the easiest method(not for me tho).If on B1 I had a formula that said "find the string "name" and write anything after it until you reach the next "" character".On C1 field I could have a formula "find the string "age" and write anything after it until you reach the next "" character.On D1 I would have the same for "height" string,then on E1 for city string.

My question is somewhat similar to this one
Extract A String Between Two Characters

Formula which outputs the data between 3rd and 4th instances of the "_" character.Can we substitute "3rd and 4th" with a specific strings like "age" or "height" ?

Code:
=TRIM(LEFT(SUBSTITUTE(MID(A1,FIND("|",SUBSTITUTE(A1,"_","|",3))+1,LEN(A1)),"_",REPT(" ",LEN(A1))),LEN(A1)))

View 3 Replies View Related

Extract Trailing String Of Characters From A URL

Dec 23, 2009

I have a problem that I just don’t seem to have the brain power to solve right now. I have a list of websites and I need to extract all characters after the last “/” in the URL. URL example:.....

I have been using the find function in conjunction with LEN and RIGHT etc but the multiple instances of “/” is causing headaches for me. Also, the trailing string of characters that I want to extract can be both text or numeric and are of differing lenths.

View 3 Replies View Related

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?

View 14 Replies View Related

Generating Random Number String Based On Alphanumeric String?

Aug 14, 2014

Wondering if it is possible to generate a random 4 digit number based off an alphanumeric string?

Example;

Cell A1 has 123XVF1234
Cell A2 has 321AFW4321

In B1 I would like to have a 4-6 digit number that is generated based on the alphanumeric data in Cell A1 (and so on down the list). If that is possible, I would also need to be able to convert back the 4-6 digit number back to its original alphanumeric value

Example;

If B1 returns 643562 it would need to be able to be converted back to 123XVF1234

View 7 Replies View Related

Rank Alphanumeric Characters?

Dec 3, 2012

I have alphanumeric characters and I have to rank them. Previously these characters were only digits (Using Rank function) and now some alphabets mixed with them. What is the function to rank Alphanumeric characters? They are like:

1A2240E01
210003004U
216000001U
499630001
463630001
400650001

View 3 Replies View Related

Separate Alphanumeric Characters

May 5, 2009

I need a macro to delete only the alphabets which are entered along with numbers. Ex: 300ML. BY running a macro i need to delete only the "ML" and the number 300 should remain the same.

Range is For Rows: Entire rows
For columns: F TO P

The cell values betwwen these ranges contains alphanumeric such as 20KG,300ML,200M ...etc

Hence i need a macro to delete the alpha charecters for the particular range.

View 6 Replies View Related

Extracting Non Alphanumeric Characters

Jan 7, 2007

Is there a way of extracting all non alphanumeric characters from a single collumn?

After extraction, I just want strings with A to Z letters, 0 to 9 numbers, spaces and hiffens(-). All in lower case.

View 9 Replies View Related

Cancatenate Then Keep Only Alphanumeric Characters

Mar 9, 2008

I am using the Concatenate() function to join strings from A1 and B1.

The result goes into C1.

How do I remove all the punctuation too?

In other words, I want to keep ONLY the upper and lower case alphanumeric characters....

View 6 Replies View Related

Change Current Cell Value If Number String NOT Letter String?

Apr 7, 2014

In sheet1 I have a simple database consisting of 5 columns of data

Column A : Name ie James Jones
Column B : payroll number ie 123456
Column C : shift times ie 1245-2124
Column D : job title ie floor
Column E : comments ie A/L or 0600-1500

what I would like is some code that will go down Column E and if a 'time string' ie 1300-2130 is found then copy this string and paste into corresponding value in column C. If a text string is found ie A/L or Sick or anything like this then ignore and move onto next cell, loop this until all cells in column E have been checked.

View 4 Replies View Related

Sort Column Of Variable Length Alphanumeric Characters?

Aug 2, 2012

I'm trying to sort by a column with one to four digit numbers, any of which may be followed by a single letter, i.e. 1,2,3 4A,5, 10,11A,75,101A,600,705,1010B,1011A.

I'd like them to sort in the above order. I am only able to have excel 2003 sort all of the numeric cells first, followed by the cells with the alpha character.

View 4 Replies View Related

Extract TEXT From Alphanumeric Cell

Oct 23, 2009

I've got a cell with codes, such as:

21ML,
43GKP,
etc.

I want these to look like :

ML21,
GKP43,

i.e. swap the numbers with the letters but keeping the order of the characters.

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

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

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

Deleting Certain Characters Until The 1st Letter Of Every Row

Apr 8, 2014

Let's say I have these 4 rows

1231-123 Lady Gaga - Applause
3123 123 -- Marvin Gaye - SEduction
1-20389@---12 Beyonce - Crazy in Love
-123 -- Andrea Bocelli - whatever

Is there any way to make Excel delete every character that is not found withing the alphabet until it find the first letter and STOP there ?

View 11 Replies View Related

Extracting Alphanumeric Value From The String

Jul 20, 2009

I have a rather difficult task to accomplish. What I need is to be able to extract an alphanumeric value from the string. It is a serial number of the equipment and it is always nine alphanumeric characters long. The problem is it can be anywhere in string. Example: “2156545 36 month lease NWL023568 Nancy Clay”. The serial number I need to extract is NWL023568.

View 7 Replies View Related

How To Get Extra Min And Max From Alphanumeric String

Aug 28, 2012

I have values in cell like,

1,2,3 & 4
10,11
8,9,10,11,12
etc.

the formula to get min and max from this string

for the first value Min = 1 and max = 4,
for the 2nd value Min = 10, and max = 11
fo the 3rd one Min = 8 and max = 12

View 3 Replies View Related

Parse AlphaNumeric String

Apr 8, 2013

Objective: To create a list of numbers based a cell which are present in an alpha-numeric string

Issues: "-" shows up at times without the context of numbers (i.e. MS-DRG)
Issues: I am not interested in float numbers (i.e. 37.63-37.66)

'Example data in cells (row 1 = A1, row 2 =A2, row 3 = A3, row 4 = A4)
MS-DRGs 767-768, 774-775
Rev 115, 125, 135, 145, 155, 655-656
Rev 174
MS-DRG 001 with ICD-9 37.63-37.66, 37.52

'Desired Results (row 1 = B1, row 2 =B2, row 3 = B3, row 4 = B4)
767,767,768,774,775
115, 125, 135, 145, 155, 655, 656
174
001

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







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