# Extracting Letters And Numbers In A Random Alphanumeric String

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

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

View 6 RepliesADVERTISEMENT

I have got to extract a series of mixed letters & numbers from a cell. The format of the data i need to extract is always [Letter][Letter][Number][Letter][Letter]][Letter][Number][Number]. The problem I've got is the notes field is not in a standard format as it can be anywhere in the cell.

The table below shows the notes cell & the Data I require.

Notes

Required Data

Fault number AB1ABC12 is complete

AB1ABC12

BC2ABC12 status is unknown

BC2ABC12

pending job ws1abc12

ws1abc12

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

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

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.

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.

i have a list of 2000 fields which have the same format IE "AB10014"

I need to remove the "AB" from every field and leave the #.

Besides putting a space and running text to columns I'm not sure how.

In column A I have the following numbers

13710

14782H

9827

14782

14206B

a formula that looks at this range of cells and returns only the cells that have a letter at the end. The letter range goes A to M

I have what seems to be a pretty complicated situation involving random numbers and letters. I need to generate random sets of this combination of 2 letters follows by 6 numbers (ex. AB123456). I need the final product to be randomly generated and non-repeated.

I have played with various methods of producing random numbers and random letters both repeated and non-repeated. The closest I can get would be a cell of randomly generated letters adjacent to a cell of randomly generated numbers which so far has been useless.

VBA or excel functions are both okay.

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.

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

Anyone know how to do this?

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

A given string of some letters (from three to five, six) is followed by a single number (1-4). Then it ends, or is followed by another such combination, up to a maximum of, let's say, four.

Example:

xxxx1

yyy3yyyy1

xyzxx1yxz4xzx1

xxx2xxx2xxx2xxx2

What I would like to do is to divide a given chain of characters into the smallest chains which contain only letters and one number. To the examples given above, I'd like to receive the following sets:

xxxx1

yyy3 and yyyy1

xyzxx1 and yxz4 and xzx1

... and so on

Is it somehow possible to do it with simple functions? Or is VB necessary (which sadly I don't know)?

I would like to take the below strings and remove any numbers and just leave letters. Or extract the payee from the string. If at all possible, I'd prefer this in a formula.

Card Purchase WAL Wal-Mart Sup 320004 0532 WAL-SAMS CENTRAL LA

Card Purchase WAL-MART #0532 053201 308 N AIRLINE HWY CENTRAL LA

Card Purchase LEBLANC'S FOOD S 874301 209 S. AIRLINE HWY CENTRAL LA[code]....

Basically, (the first line above) I'd like to remove the words "Card Purchase" and any numbers...or preferably just leave "Wal-Mart Sup" instead of the full string. I'd like the product of the first line to match the product of the 2nd line.

I am in need of a formula that will separate a string of text. Specifically, I want to pull out the number values (including cents) from the rest of the text. This is how the text reads:

10.00 OVERDRAFT PROTECTION TRANSFER FEE

10.00

56.00 ELECTRONIC/ACH DEBIT

56.00

199.00 ELECTRONIC/ACH DEBIT

199.00

2,017.64 ELECTRONIC/ACH DEBIT

2,017.64

The data to the left is the data I am working with and the I want the results to read similar to the results on the right.

So for a program I am writing, the user inputs a text string of 200+ alphabetic letters. Only 20 of the 26 alphabet can be in this input (excluding BJOUXZ). What I need to have a macro or function do is check to make sure none of these 6 alphabet letters are present in the string, and also check to make sure no numbers are present either.

Also, I would love for the input text to be converted to Uppercase for all letter as it does this check.

I would like to extract the digits below in red. The variables are the digits in all of the sections vary from a minimum of one digit to the maximum of three digits.

111.222.333.444

I have not managed to find exactly what I am looking for so far..

I have cells with a mix of numbers and strings and I would like to extract certain numbers only.

I.E.

123a bcd8

acbd 1234e

I would like to only extract the first instance of numbers - so in example 1 - 123 and in example 2 - 1234. I have found various methods of extracting all numbers such as:

Function ExtractNums(r As String) As String

'Creates and returns a reference for a regular expression object

With CreateObject("vbscript.regexp")

'Sets the pattern to be non-digits

.Pattern = "D"

'We want to find all matching non-digits

.Global = True

'Replace all non-digits with an empty string and return this value for the function

ExtractNums = .Replace(r, "")

End With

End Function

I have a woorkbook where I have a cell that has the following apperance:

12345678-ABCDEFG

The numbers can vary in length and as well as the text. What I am trying to do is to split this cell into to columns. Column 1 shows the account number, Column 2 should show the Text and the "-" sign should not be in any of the columns ie it should look something like this

Column1 Column2

12345678 ABCDEFG

I have found a useful function for the first part thanx to Mikerickson

URL:Extract Numerical Data

Function midNumber(inputStr As String) As Double

Dim i As Long

For i = 1 To Len(inputStr)

midNumber = CDbl(Val(Mid(inputStr, i)))

If midNumber <> 0 Then Exit Function

Next i

End Function

But how do i seperate the text and how do Crete a macro that would loop through some 2000 rows and create a column A and B from Column A?

I've been using excel for quite sometime but have never tried the macro system or posting VBA code so I'm wanting to get my feet wet with a little project.

I want to make a button on a sheet that when pressed generates a random number between 3 and 18, ideally it would make a new set of random numbers if pressed again.

I know the randbetween portion of my code will not work - but how can I make this work with VBA? I need four random numbers added after the 4 string characters.

For Each R In MyNewR

If IsEmpty(R) Then

R.Value = Right(R.Offset(0, -1), 4) & _

Application.WorksheetFunction.randbetween(1, 9)

End If

Next R

I have a few columns of letters (ranging from A to Z) - onyl one letter per cell. I need to be able to deduct them such that they result in a numeric difference (ie D-B=2, D-A=3). Is there any way this can be done in VBA? (The actual columns are in a word table, so would be great if there was a VBA way to do this, not an excel function).

View 6 Replies View RelatedIs 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.

I receive an extraction from AutoCAD that lists the electrical devices in a drawing. I don't have any problems extracting the letters. I have a problem extracting the device number and the device number extension.

The device label extraction is similar to this:

DCM1005-1

DCM1005-10

DCM1005A

MTR1005-1

MTR1005-10

MTR1005A

I want to create 3 columns from the device label: (I separated the column with commas)

A1, B1, C1, D1

DCM1005-1, DCM, 1005, 1

DCM1005-10, DCM, 1005, 10

DCM1005A, DCM, 1005, A

MTR1005-1, MTR, 1005, 1

MTR1005-10, MTR, 1005, 10

MTR1005A, MTR, 1005, A

I'm pulling phone numbers out of text strings. There is text string ("Office:") indicating that the number following is an office number (the number that I need). MID and FIND take care of this. There are sometimes two instances of office numbers in single text string, so adding a second column using the third argument of FIND lets me start another search past the first instance of an office number. We have a bunch of office numbers with the qualifier "(Text)" after the original "Office" but before the phone number. How do I catch these ones?

View 8 Replies View RelatedAny code that can extract the alpha numeric values from sample spreadsheet below?

Where in col A "SCn" is extracted (or copied) and then pasted in col H?

n = 1 to 99

That is - from this:

A

B

C

D

E

F

G

H

I

J

K

L

[Code] .........

To this:

A

B

C

D

E

F

G

H

I

J

K

L

[Code] ......

There are thousands of rows to extract the alpha numeric values from.

I have a column of data that contains text such as 'as per A3', 'B4 requested' and 'as per F6 Mark' (these aren't cell references).

I'm trying to find a formula that will just strip out the 'A3', 'B4' and 'F6' element of the text. I've tried SEARCH with wildcards but it's not working, and can;t use the LEFT RIGHT or MID functions due to the inconsistent data.

I have a column of cells, some blank, some containing just numbers, some containing just letters, some containing numbers preceded by the the letter 'p'

E.g.

frt

34.2

36

p34.5

In the cells containing the number preceded by the 'p' - i would like to remove the 'p' leaving just the number, with all other cells remaining unchanged.

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