I have a cell with text and number (abc123).
I like to change only number 123 to percentage (abc12.3%).
Is it possible to do?
Thank you.
Asked
Active
Viewed 92 times
-2
Martijn Pieters
- 1,048,767
- 296
- 4,058
- 3,343
qplo
- 21
- 4
-
4How do you know it's 12.3% and not 1.23% or 123% or .123%? – BruceWayne Aug 28 '18 at 19:53
-
1is your data always three letters followed by a three digit number? – U3.1415926 Aug 28 '18 at 20:02
1 Answers
1
For your specific example you can use SUBSTITUTE function SUBSTITUTE(Text,Old_text,New_text).
Text Required. The text or the reference to a cell containing text for which you want to substitute characters.
Old_text Required. The text you want to replace.
New_text Required. The text you want to replace old_text with.
Try this:
put abc123 in cell A1
and in cell A2 this formula:
=SUBSTITUTE(A1,RIGHT(A1,LEN(A1)-MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"0123456789"))+1),RIGHT(A1,LEN(A1)-MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"0123456789"))+1)/10&"%")
Text: A1
Old_text: RIGHT(A1,LEN(A1)-MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"0123456789"))+1)
New_text: RIGHT(A1,LEN(A1)-MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"0123456789"))+1)/10&"%"
RIGHT(A1,LEN(A1)-MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"0123456789"))+1) extracts the number. You can do whatever operation with the number like /1000 and also use the function ROUND it will be:
ROUND(RIGHT(A1,LEN(A1)-MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"0123456789"))+1)/1000,2) = 0.12
The final function will be:
=SUBSTITUTE(A1,RIGHT(A1,LEN(A1)-MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"0123456789"))+1),ROUND(RIGHT(A1,LEN(A1)-MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"0123456789"))+1)/1000,2)&"%")
virtualdvid
- 2,323
- 3
- 14
- 32
-
-
One quick , use the same SUBSTITUTE function above, can we round up 2 decimal (0.123% become 0.12%). Thank you. – qplo Aug 29 '18 at 13:17
-
-
Wow, it works, but the % is missing at the end on ROUND(RIGHT(A1,LEN(A1)-MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"0123456789"))+1)/1000,2). Can you help. Thank you. – qplo Aug 29 '18 at 16:14
-