Need help extracting word in string that must be combination of numbers and letters

[mark63]

Hello, newbie here that is stumped!

I am looking for a formula that would return the word in a string of words that has both numbers and letters combined into one word.

eg..........in cell A1 - "Front Comp 02 Stn Mh20A Fixture 13"
would return in B1 - "Mh20A" (the only word with #'s and letters)

Other rules that may help...

It will be the ONLY word with combination characters.
The string in A1 contains various numbers of words but all are separated by single space.
The number component will always be 2-digit.
There will only be a max of 2 characters before number and 1 character after number.
Therefore the min length is 3 and max length is 5

Good Examples .... 30a, mh30, mh30a, m30, m30a

Thanks for any help.

 

[Nine Zero]

Here you go try this out

Sub CheckAlphaNumeric()
Dim sentence As String
Dim w As Variant
Dim r As Range
Dim listing() As String
Dim lrow As Integer


lrow = ActiveSheet.Cells(ActiveSheet.Rows.Count, "A").End(xlUp).Row
For Each r In Range("A1:A" & lrow)
    listing() = Split(r.Text, " ")
        For Each w In listing()
            If w Like "*[a-z][0-9]*" Or w Like "*[a-z][0-9][a-z]*" Or w Like "*[0-9][a-z]*" Then
                r.Offset(, 1).Value = w
            End If
        Next w
Next r
End Sub

I just now saw you mentioned you were a newbie.... dont know if newbie to site or Excel itself
If you do not know how to input this code in your sheet follow these steps

Open your workbook and go to the sheet where you data is
Press ALT + F11
This will open the code editor
In the top banner select Insert and select Module
You will get an empty window, simply copy the code i gave you and paste it there
Then click the green run arrow at the top and you are all set!

In order to save the workbook now it will have to be changed from either .xls or .xlsx to .xlsm as you have now added a macro

 

[mark63]

Thanks for your response. I am experienced in Excel and learning VBA so no problem pasting your code. However I found a solution when I searched Google for "Alphanumeric" (the proper term for words with number-letter combinations) I found the answer right here in this amazing forum. I am not sure how recursion formulas work but I am going to look into them.

The link below refers to a User Defined Function that returns the 1st alphanumeric word of A1 into B1 and if you copy to C1,D1,E1 etc. you get the 2nd,3rd,4th...... occurrence of an Alphanumeric word in the A1 word list.

It works as expected and saved me a lot of pain and suffering....check it out. Thanks anyways

https://www.mrexcel.com/forum/excel...ode-find-only-alphanumeric-word-sentence.html

 

[markmzz]

Hello, newbie here that is stumped!

I am looking for a formula that would return the word in a string of words that has both numbers and letters combined into one word.

eg..........in cell A1 - "Front Comp 02 Stn Mh20A Fixture 13"
would return in B1 - "Mh20A" (the only word with #'s and letters)

Other rules that may help...

It will be the ONLY word with combination characters.
The string in A1 contains various numbers of words but all are separated by single space.
The number component will always be 2-digit.
There will only be a max of 2 characters before number and 1 character after number.
Therefore the min length is 3 and max length is 5

Good Examples .... 30a, mh30, mh30a, m30, m30a

Thanks for any help.

Hi!

Try the Array Formula (use Ctrl+Shift+Enter to enter the formula) below too:

=TRIM(RIGHT(SUBSTITUTE(TRIM(REPLACE(A1&" ",
SMALL(IF(MMULT(--ISNUMBER(1/LOOKUP(CODE(UPPER(MID($A1,IF(LEN(TRIM(MID($A1,ROW($1:$99),2)))=2,
IF(ISNUMBER(-MID($A1,ROW($1:$99),2)),ROW($1:$99)+{-1,2})),1))),{1;65;91},{0;1;0})),{1;1}),
IF(LEN(TRIM(MID($A1,ROW($1:$99),2)))=2,IF(ISNUMBER(-MID($A1,ROW($1:$99),2)),ROW($1:$99)))),1)+3,99,""))," ",REPT(" ",99)),99))



[TABLE="class: grid, width: 347"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Front Comp 02 Stn Mh20A Fixture 13[/TD]
[TD]Mh20A[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Front Comp 02 Stn Mh20A[/TD]
[TD]Mh20A[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Front Comp 02 Stn Mh20[/TD]
[TD]Mh20[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Front Comp 02 Stn h20A Fixture 16[/TD]
[TD]h20A[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Front Comp 02 Stn 30a Fixture 17[/TD]
[TD]30a[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]Front Comp 02 Stn mh30 Fixture 18[/TD]
[TD]mh30[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]Front Comp 02 Stn mh30a Fixture 19[/TD]
[TD]mh30a[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]Front Comp 02 Stn m30 Fixture 20[/TD]
[TD]m30[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]Front Comp 02 Stn m30a Fixture 21[/TD]
[TD]m30a[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]***[/TD]
[TD]*********************************[/TD]
[TD]********[/TD]
[TD]**[/TD]
[/TR]
</tbody>[/TABLE]


Markmzz

 

[dave2018]

Hi!

Try the Array Formula (use Ctrl+Shift+Enter to enter the formula) below too:

=TRIM(RIGHT(SUBSTITUTE(TRIM(REPLACE(A1&" ",
SMALL(IF(MMULT(--ISNUMBER(1/LOOKUP(CODE(UPPER(MID($A1,IF(LEN(TRIM(MID($A1,ROW($1:$99),2)))=2,
IF(ISNUMBER(-MID($A1,ROW($1:$99),2)),ROW($1:$99)+{-1,2})),1))),{1;65;91},{0;1;0})),{1;1}),
IF(LEN(TRIM(MID($A1,ROW($1:$99),2)))=2,IF(ISNUMBER(-MID($A1,ROW($1:$99),2)),ROW($1:$99)))),1)+3,99,""))," ",REPT(" ",99)),99))



[TABLE="class: grid, width: 347"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Front Comp 02 Stn Mh20A Fixture 13[/TD]
[TD]Mh20A[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Front Comp 02 Stn Mh20A[/TD]
[TD]Mh20A[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Front Comp 02 Stn Mh20[/TD]
[TD]Mh20[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Front Comp 02 Stn h20A Fixture 16[/TD]
[TD]h20A[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Front Comp 02 Stn 30a Fixture 17[/TD]
[TD]30a[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]Front Comp 02 Stn mh30 Fixture 18[/TD]
[TD]mh30[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]Front Comp 02 Stn mh30a Fixture 19[/TD]
[TD]mh30a[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]Front Comp 02 Stn m30 Fixture 20[/TD]
[TD]m30[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]Front Comp 02 Stn m30a Fixture 21[/TD]
[TD]m30a[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]***[/TD]
[TD]*********************************[/TD]
[TD]********[/TD]
[TD]**[/TD]
[/TR]
</tbody>[/TABLE]


Markmzz

Markmzz, is there a way to make your formula work for any length alphanumeric text in a cell?

so:

"1234 Her365TFg 4664 pe-t" returns "Her365TFg"
"1235 Her 35542 43h e47-h" returns {"43h","e47-h"}

(as you see, i need all the instances in an array when the formula evaluates)

Thanks!

 

[István Hirsch]

And how many alphanumeric texts may occur per cell (maximum)?

 

[markmzz]

Markmzz, is there a way to make your formula work for any length alphanumeric text in a cell?

so:

"1234 Her365TFg 4664 pe-t" returns "Her365TFg"
"1235 Her 35542 43h e47-h" returns {"43h","e47-h"}

(as you see, i need all the instances in an array when the formula evaluates)

Thanks!

Hi Dave2018!

I think that the formula can work with others numbers of digits (I didn't test it in depth), but only extract the first group and only group with letters (A-Z).

Here is the array formula:

=TRIM(RIGHT(SUBSTITUTE(TRIM(REPLACE(A1&" ",
SMALL(IF(MMULT(--ISNUMBER(1/LOOKUP(CODE(UPPER(MID($A1,IF(LEN(TRIM(MID($A1,ROW($1:$99),n)))=n,
IF(ISNUMBER(-MID($A1,ROW($1:$99),n)),ROW($1:$99)+{-1,n})),1))),{1;65;91},{0;1;0})),{1;1}),
IF(LEN(TRIM(MID($A1,ROW($1:$99),n)))=n,IF(ISNUMBER(-MID($A1,ROW($1:$99),n)),ROW($1:$99)))),1)+n+1,99,""))," ",REPT(" ",99)),99))

Do some tests.

Ps: I think that only this example "1234 Her365TFg 4664 pe-t" returns "Her365TFg" can be done like you want.<strike></strike>

Markmzz

 

[markmzz]

And how many alphanumeric texts may occur per cell (maximum)?

Hi!

I'm sorry, but I don't know (I didn't test it in depth).

So, if you want, do some tests and tell me the results.

By the way, do some tests with my last suggestion too.

Markmzz

 

[markmzz]

Hi!

A small modification in the Array Formulas (use Ctrl+Shift+Enter to enter the formula):

=TRIM(RIGHT(SUBSTITUTE(TRIM(REPLACE(A1&" ",
SMALL(IF(MMULT(--ISNUMBER(1/LOOKUP(CODE(UPPER(MID($A1,IF(LEN(TRIM(MID($A1,ROW($1:$99),2)))=2
,IF(ISNUMBER(-MID($A1,ROW($1:$99),2)),ROW($1:$99)+{-1,2})),1))),{1;65;91},{0;1;0})),{1;1}),
ROW($1:$99)),1)+3,99,""))," ",REPT(" ",99)),99))

=TRIM(RIGHT(SUBSTITUTE(TRIM(REPLACE(A1&" ",
SMALL(IF(MMULT(--ISNUMBER(1/LOOKUP(CODE(UPPER(MID($A1,IF(LEN(TRIM(MID($A1,ROW($1:$99),n)))=n,
IF(ISNUMBER(-MID($A1,ROW($1:$99),n)),ROW($1:$99)+{-1,n})),1))),{1;65;91},{0;1;0})),{1;1}),
ROW($1:$99)),1)+n+1,99,""))," ",REPT(" ",99)),99))

Markmzz