CODEWITHSUNDEEP
excelexcel-formula
Tuberose
Tuberose

Reputation: 444

Substitute with multiple old_text

A1 may contains one of this two values:

  1. Person 2
  2. Numbers 2

I need returns in A2:

  1. 2
  2. 2

I have tried =TRIM(SUBSTITUTE(J23,"Persons",""))

How can assign multiple values in old_text field?

Upvotes: 1

Views: 3987

Answers (1)

shrivallabha.redij
shrivallabha.redij

Reputation: 5902

You can nest SUBSTITUTE function if you know all possible substitutes and they are not too many like below:

=TRIM(SUBSTITUTE(SUBSTITUTE(A1,"Person",""),"Numbers",""))

Or you can choose approach like below which is based on your posted sample which assumes numeric part is always on the right hand side.

=MID(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"0123456789",1)),LEN(A1))

Hope this helps!

Upvotes: 4

Related Questions