# If A1 starts with h_, remove h_all numbers following.

#### Simon4s

##### Board Regular
I need the product code of a list our company's internal listing. All codes start with letters.
Some of the codes look like this: h1_231B01EJD or H1_3431B02eJF.

I want to remove h1_ALLnumbers following. Is there away to do replace all h1*numbers?

Any solution appreciated.

ALSO
Some cells look like this: 123ABC123.

To sum up my question, I want to get rid of all numeric prefixes and all "h1_numbers"
123ABC123
h1_231B01EJD
H1_3431B02eJF

EDIT: This would work even better:

Return the first numeric sequence from RIGHT side AND return ALPHA sequence from right side.

Last edited:

### Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.

#### aRandomHelper

##### Active Member
Maybe this could work? Need to enter as array formula (Ctrl + Shift + Enter)

Book1.xlsm
AB
2123ABC123ABC123
3h1_231B01EJDB01EJD
4H1_3431B02eJFB02eJF
Sheet7
Cell Formulas
RangeFormula
B2:B4B2=MID(SUBSTITUTE(SUBSTITUTE(A2,"H1_",""),"h1_",""),LEN(LEFT(SUBSTITUTE(SUBSTITUTE(A2,"H1_",""),"h1_",""),MATCH(FALSE,ISNUMBER(--MID(SUBSTITUTE(SUBSTITUTE(A2,"H1_",""),"h1_",""),ROW(\$1:\$93),1)),0)-1))+1,99)
Press CTRL+SHIFT+ENTER to enter array formulas.

#### Zot

##### Well-known Member
If you want a UDF
Excel Formula:
Function XCut(rng As Range)
If Left(rng, 1) = "H" Then
x = Right(rng, Len(rng) - 3)
Else
x = rng
End If
For i = 1 To Len(rng)
If Not IsNumeric(Mid(x, i, 1)) Then Exit For
Next
XCut = Right(x, Len(x) - i + 1)
End Function

#### Rick Rothstein

##### MrExcel MVP
If you are up for using a UDF (user defined function), this is how I would write it...
VBA Code:
Function XCut(S As String) As String
XCut = Replace(S, "h1_", "", , , vbTextCompare)
XCut = Split(XCut, Val(XCut), 2)(1)
End Function
HOW TO INSTALL UDFs
------------------------------------
If you are new to UDFs, they are easy to install and use. To install it, simply press ALT+F11 to go into the VB editor and, once there, click Insert/Module on its menu bar, then copy/paste the above code into the code window that just opened up. That's it.... you are done. You can now use XCut just like it was a built-in Excel function. For example,

=XCut(A1)

If you are using XL2007 or above, make sure you save your file as an "Excel Macro-Enabled Workbook (*.xlsm) and answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

Zot

#### Zot

##### Well-known Member

ADVERTISEMENT

If you are up for using a UDF (user defined function), this is how I would write it...
VBA Code:
Function XCut(S As String) As String
XCut = Replace(S, "h1_", "", , , vbTextCompare)
XCut = Split(XCut, Val(XCut), 2)(1)
End Function
HOW TO INSTALL UDFs
------------------------------------
If you are new to UDFs, they are easy to install and use. To install it, simply press ALT+F11 to go into the VB editor and, once there, click Insert/Module on its menu bar, then copy/paste the above code into the code window that just opened up. That's it.... you are done. You can now use XCut just like it was a built-in Excel function. For example,

=XCut(A1)

If you are using XL2007 or above, make sure you save your file as an "Excel Macro-Enabled Workbook (*.xlsm) and answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.
Short and way simpler.

Actually one reason to use UDF instead of long long formula is that it will keep workbook smaller and response faster if there are many rows of same formula, in my opinion.

#### Rick Rothstein

##### MrExcel MVP
If the UDF that I posted in Message #4 is not usable for you, here is a shorter array-entered** formula that you can use...
Excel Formula:
=MID(A1,MIN(IF(NOT(ISNUMBER(0+MID(SUBSTITUTE(A1,"_",0),ROW(\$2:\$99),1))),ROW(\$2:\$99))),99)
**Commit this formula using CTRL+SHIFT+ENTER and not just Enter by itself

#### jtakw

##### Well-known Member
Hi,

Based only on your few samples, this may work:

Book3.xlsx
AB
1123ABC123ABC123
2h1_231B01EJDB01EJD
3H1_3431B02eJFB02eJF
Sheet923
Cell Formulas
RangeFormula
B1:B3B1=MID(A1,MIN(SEARCH(CHAR(ROW(\$65:\$90)),A1&CHAR(ROW(\$65:\$90)),2)),99)
Press CTRL+SHIFT+ENTER to enter array formulas.

Replies
7
Views
121
Replies
5
Views
150
Replies
6
Views
117
Replies
11
Views
384
Replies
3
Views
359

Threads
1,141,204
Messages
5,704,951
Members
421,372
Latest member
Jamie11

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

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