Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Find and Add a number in a text field
I have a list of activity IDs that I need to add a number or a letter before
a period (.) Here's examples: Orig Activity ID 1.1.1.2 2.5.8.9 4.5.9.6.7.1.2 10.25.20.20.10.50 I want to find a formula that would put a zero (0) before each period (.) and a zero after the last number in the field in the activity ID. So after the formula it would look like this: 10.10.10.20; 20.50.80.90; 40.50.90.60.70.10.20; 100.250.200.200.100.500 Any help would be greatly appreciated. Thanks. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Find and Add a number in a text field
=SUBSTITUTE(A1,".","0.")&"0"
Bob Umlas Excel MVP "dereksmom" wrote in message ... I have a list of activity IDs that I need to add a number or a letter before a period (.) Here's examples: Orig Activity ID 1.1.1.2 2.5.8.9 4.5.9.6.7.1.2 10.25.20.20.10.50 I want to find a formula that would put a zero (0) before each period (.) and a zero after the last number in the field in the activity ID. So after the formula it would look like this: 10.10.10.20; 20.50.80.90; 40.50.90.60.70.10.20; 100.250.200.200.100.500 Any help would be greatly appreciated. Thanks. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Trouble sorting first by a dates field and then by a text field. | Excel Discussion (Misc queries) | |||
how do i use VLOOKUP to find text and return a number? | Excel Discussion (Misc queries) | |||
find number position in excel text cell | Excel Discussion (Misc queries) | |||
Number Field to Text or Words | Excel Discussion (Misc queries) | |||
Find all text instances in a sheet and add one number from each row | Excel Discussion (Misc queries) |