If A1 starts with h_, remove h_all numbers following.

Simon4s

Board Regular
Joined
Sep 22, 2014
Messages
152
Office Version
  1. 2016
  2. 2013
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
Joined
Jan 14, 2021
Messages
301
Office Version
  1. 2016
Platform
  1. Windows
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
Joined
Nov 26, 2020
Messages
1,550
Office Version
  1. 2016
Platform
  1. Windows
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
Joined
Apr 18, 2011
Messages
37,345
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows
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.
 
  • Like
Reactions: Zot

Zot

Well-known Member
Joined
Nov 26, 2020
Messages
1,550
Office Version
  1. 2016
Platform
  1. Windows

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
Joined
Apr 18, 2011
Messages
37,345
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows
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
Joined
Jun 29, 2014
Messages
6,007
Office Version
  1. 2016
Platform
  1. Windows
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.
 

Forum statistics

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

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
Top