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

M

mark63

New Member
Joined
Mar 30, 2018
Messages
2
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.
 

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".
Sort by date Sort by votes
Here you go try this out

Code: 
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
 
Last edited:
Upvote 0
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
 
Upvote 0
mark63 said:
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.
Click to expand...

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
 
Last edited:
Upvote 0
markmzz said:
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
Click to expand...

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!
 
Upvote 0
dave2018 said:
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!
Click to expand...

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
 
Upvote 0
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
 
Upvote 0
You must log in or register to reply here.

Similar threads

nburaq
  • Solved
Find a word combination of letters and numbers
Replies
5
Views
456
Peter_SSs
Peter_SSs
C
  • Solved
Extract 8 character number from text string
2
Replies
15
Views
742
Fluff
Fluff
J
  • Locked
  • Question Question
Formula Text String Help - Extract a sequence of numbers/letters from a text string
Replies
1
Views
2K
Fluff
Fluff
J
  • Question Question
VBA Help: Macro finds text string, but need it to find whole words ONLY
Replies
5
Views
1K
DanteAmor
DanteAmor
A
  • Question Question
Determine Point of Cell Word Wrap of A String (max length of string permitted for a static sized cell)
Replies
5
Views
1K
Micron
M

Forum statistics

Threads
1,223,905
Messages
6,175,297
Members
452,633
Latest member
DougMo

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top