Remove Numerics From Text

[Admin]

Hi Vimal,

Welcome to board !!

One way would be

=REPLACE(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"012 3456789")),255,"")

I assume that no other text will be there after the numerics.


https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
https://eileenslounge.com/viewtopic.php?f=27&t=35521&p=276185#p276185
https://eileenslounge.com/viewtopic.php?p=276185#p276185
https://eileenslounge.com/viewtopic.php?p=276185#p276185
https://eileenslounge.com/viewtopic.php?p=276673#p276673
https://eileenslounge.com/viewtopic.php?p=276751#p276751
https://eileenslounge.com/viewtopic.php?p=276754#p276754
https://eileenslounge.com/viewtopic.php?f=30&t=35100&p=274367#p274367
https://eileenslounge.com/viewtopic.php?p=274368#p274368
https://eileenslounge.com/viewtopic.php?p=274370#p274370
https://eileenslounge.com/viewtopic.php?p=274578#p274578
https://eileenslounge.com/viewtopic.php?p=274577#p274577
https://eileenslounge.com/viewtopic.php?p=274474#p274474
https://eileenslounge.com/viewtopic.php?p=274579#p274579
https://www.excelfox.com/forum/showthread.php/261-Scrolling-Marquee-text-on-Userform?p=864&viewfull=1#post864
https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA

[Rick Rothstein]

One way would be

=REPLACE(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"012 3456789")),255,"")

I assume that no other text will be there after the numerics.

For the given example, your formula would leave a trailing blank space in the returned value. Assuming collapsing possible multiple adjacent spaces inside the text would not be a problem, and also assuming there may or may not be a space between the last text and first digit, then wrapping your formula in a TRIM function would solve the problem...

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

If there could be multiple adjacent internal spaces that would need to be preserved, then this array-entered** formula could be used to get rid of any trailing spaces that might result from your formula...

=LEFT(REPLACE(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1 &"012 3456789")),255,""),MAX((MID(REPLACE(A1,MIN(FIND({0 ,1,2,3,4,5,6,7,8,9},A1&"012 3456789")),255,"")&REPT(" ",255),ROW($1:$255),1)<>" ")*ROW($1:$255)))

**Commit this formula using CTRL+SHIFT+ENTER and not just Enter by itself