Removing specific characters from end of a text

[dankar]

Hi, i have a list of 15k row, i want to remove all the ones ending with "_number" like _1 , _5, _99

the number after the underscore could be from 1 to 99. I need the last underscore and any number after it to be removed.

The result should be like this for this example:

thank you

 

[DanteAmor]

With formula:

varios 08sep2023.xlsm
	A	B
1
2	2060_0500	2060_0500
3	2060_0500_1	2060_0500
4	2060_0500_2	2060_0500
5	2060_0500_99	2060_0500
6	10152_08122	10152_08122
7	10152_08122_1	10152_08122
8	10152_08122_2	10152_08122
Hoja1

Cell Formulas
Range	Formula
B2:B8	B2	=IF(LEN(A2)-LEN(SUBSTITUTE(A2,"_",""))=2,TRIM(LEFT(SUBSTITUTE(A2,"_",REPT(" ",99),2),99)),A2)

With macro:

Sub Removing_specific_characters()
  With Range("A2", Range("A" & Rows.Count).End(3))
    .Value = Evaluate("=IF({1},IF(LEN(" & .Address & ")-LEN(SUBSTITUTE(" & .Address & ",""_"",""""))=2,TRIM(LEFT(SUBSTITUTE(" & .Address & ",""_"",REPT("" "",99),2),99))," & .Address & "))")
  End With
End Sub

--------------
Let me know the result and I'll get back to you as soon as I can.
Cordially
Dante Amor
--------------

 

[dankar]

=IF(LEN(A2)-LEN(SUBSTITUTE(A2,"_",""))=2,TRIM(LEFT(SUBSTITUTE(A2,"_",REPT(" ",99),2),99)),A2)

Sorry this is what i got :

 

[dankar]

Part of the actual list:

ID

SAM_19517_03052010

SAM_19517_10262016

SAM_19517_10262016_1

SAM_19517_10282016

SAM_19517_10282016_1

SAM_19517_10282016_2

SAM_19517_10282016_3

SAM_19517_10282016_4

SAM_19517_10282016_5

SAM_19517_10282016_6

SAM_19517_10282016_7

SAM_19517_10282016_8

SAM_19517_10282016_9

SAM_19517_10282016_10

BOB_21766_7052_09212009

BOB_21766_7052_09212009_1

BOB_21766_7052_09212009_2

BOB_21766_7052_09212009_3

BOB_21766_7052_09212009_4

BOB_21766_7052_09212009_5

BOB_21766_7052_09212009_6

BOB_21766_7052_09212009_7

BOB_21766_7052_09212009_8

BOB_21766_7052_09212009_9

BOB_21766_7052_09212009_10

BOB_21766_7052_09212009_11

BOB_21766_7052_09212009_12

BOB_21766_7052_09212009_13

BOB_21766_7052_09212009_14

BOB_21766_7052_09212009_15

BOB_21766_7052_09212009_16

BOB_21766_7052_09212009_17

BOB_21766_7052_09212009_18

BOB_21766_7052_09212009_19

BOB_21766_7052_09212009_20

BOB_21766_7052_09212009_21

BOB_21766_7052_09212009_22

 

[DanteAmor]

The examples in post #1 are not the same as the examples in post #4.

So your data always starts with 3 letters and an underscore?

SAM_
BOB_
etc_

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[dankar]

The examples in post #1 are not the same as the examples in post #4.

So your data always starts with 3 letters and an underscore?

SAM_
BOB_
etc_

Sorry, i thought the start don't matter because what I want to remove only what's at the end.

the list has different starts and length, could be anything. but all at the end of the text, either doesn't have the "_number" or ends with "_number" and the number could be either one digit or 2 digits.

what i want, regardless of what the list start with or the length, i just want to remove anything ends with underscore and any number after it.

so, at the end all will be without underscore number (_number) in the end.



hope i clarified it well and sorry again.

 

[Fluff]

How about

=IF(LEN(TEXTAFTER(A2,"_",-1,,1))<4,TEXTBEFORE(A2,"_",-1),A2)

 

[dankar]

How about

=IF(LEN(TEXTAFTER(A2,"_",-1,,1))<4,TEXTBEFORE(A2,"_",-1),A2)

Thank you, worked perfect!

 

[Fluff]

Glad we could help & thanks for the feedback.