Nesting SUBSTITUTE functions
Hello All,
I need help!!! I am trying to write a formula that replaces the letter "a" with the number "1", "b" with "2", "c" with "3", etc.... The individual formula of (ex.) =SUBSTITUTE([Column3]1, "a", "1") is easy enough, but for whatever reason I can't seem to figure out how to nest it with the rest of the letters and numbers. I have tried every different way I can think of, but nothing seems to work. My end goal is that "abc" automatically becomes "123" in the target cell. I do not however want to limit how many times each particular letter can be replaced. That way if I have "abcbacb" it turns into "1232132". Any ideas???
Thanks!
Comments
-
There is a way to do it with a combination of VLOOKUP and MID formulas.
First, make a table:
COL1__COL 2
A ________1
B________2
C________3
and so on...
AND FORMULA IS:
=VLOOKUP(MID([TEXT CELL]1, 1, 1), [COL1]1:[COL2]3, 2, false) + ""
VLOOKUP(MID([TEXT CELL]1, 2, 1), [COL1]1:[COL2]3, 2, false) +
VLOOKUP(MID([TEXT CELL]1, 3, 1), [COL1]1:[COL2]3, 2, false) +
AND SO ON..,
Note that you need to put "" only once - (this will make a string and will "put" another number after the previous instead of adding them)
You need to repeat this formula as many times as long as the text is because each line changes only one letter.
[TEXT CELL]1 - the reference to your cell
-
Awesome! Thank you much!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.7K Get Help
- 433 Global Discussions
- 136 Industry Talk
- 468 Announcements
- 4.9K Ideas & Feature Requests
- 143 Brandfolder
- 147 Just for fun
- 64 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 298 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!