If A1 starts with h_, remove h_all numbers following.

[Simon4s]

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.

 

[aRandomHelper]

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

Book1.xlsm
	A	B
2	123ABC123	ABC123
3	h1_231B01EJD	B01EJD
4	H1_3431B02eJF	B02eJF
Sheet7

Cell Formulas
Range	Formula
B2:B4	B2	=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]

If you want a UDF

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]

If you are up for using a UDF (user defined function), this is how I would write it...

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]

If you are up for using a UDF (user defined function), this is how I would write it...

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]

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

=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]

Hi,

Based only on your few samples, this may work:

Book3.xlsx
	A	B
1	123ABC123	ABC123
2	h1_231B01EJD	B01EJD
3	H1_3431B02eJF	B02eJF
Sheet923

Cell Formulas
Range	Formula
B1:B3	B1	=MID(A1,MIN(SEARCH(CHAR(ROW($65:$90)),A1&CHAR(ROW($65:$90)),2)),99)
Press CTRL+SHIFT+ENTER to enter array formulas.