| Home |
| Search |
| Today's Posts |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Removing characters from a cell (keeping only numbers) 2
Cool..,
This new formula is perfect.. Many thanks to all (Again) Monk :0) "bob777" wrote: Assuming your unwanted character is an €œa€ and that your data is in column A starting in row 1, type this formula into cell B1 and copy it down. This deals with all 4 possibilities a 77999a a77999 77999a 77999 IF(LEFT(CONCATENATE(LEFT(a1),RIGHT(a1)))="a",IF(RI GHT(CONCATENATE(LEFT(a1),RIGHT(a1)))="a",MID(a1,2, LEN(a1)-2),MID(a1,2,LEN(a1)-1)),IF(RIGHT(CONCATENATE(LEFT(a1),RIGHT(a1)))="a", LEFT(a1,LEN(a1)-1),a1)) I have posted this so that you can see how to deal with numbers and text strings of varying length. Bobf -- bob777 ------------------------------------------------------------------------ bob777's Profile: http://www.excelforum.com/member.php...o&userid=28504 View this thread: http://www.excelforum.com/showthread...hreadid=504430 |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Removing characters from a cell (keeping only the numbers) | Excel Discussion (Misc queries) | |||
| Adding numbers in one cell and showing total in seperate cell | Excel Discussion (Misc queries) | |||
| create a cell that accumulates, numbers from another cell... | Excel Discussion (Misc queries) | |||
| Averaging Numbers when 2 numbers in one cell | Excel Worksheet Functions | |||
| GET.CELL | Excel Worksheet Functions | |||
Threaded Mode
