Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
LEFT &RIGHT formula on the same cell
I was trying to take off hyphen within a text string, such as "0100-00-000".
I used LEFT formula to return the first 4 digit. Then I used RIGHT formula on the same cell to return the last 3 digit but somehow the formula doesn't work. It won't give me a error message but just keep my formula in the cell. Please help and thanks |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
LEFT &RIGHT formula on the same cell
Format the cell as General and re-enter the formula.
If you post again, do supply the formula. -- Kind regards, Niek Otten "Bonnie W." <Bonnie wrote in message ... I was trying to take off hyphen within a text string, such as "0100-00-000". I used LEFT formula to return the first 4 digit. Then I used RIGHT formula on the same cell to return the last 3 digit but somehow the formula doesn't work. It won't give me a error message but just keep my formula in the cell. Please help and thanks |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
LEFT &RIGHT formula on the same cell
Assuming that the string value is in A1, the following formula will strip the
hyphens from the value: =LEFT(A1,FIND("-",A1,1)-1)&MID(A1,FIND("-",A1,1)+1,FIND("-",A1,FIND("-",A1,1)+1)-(FIND("-",A1,1)+1))&RIGHT(A1,3) -- Kevin Backmann "Bonnie W." wrote: I was trying to take off hyphen within a text string, such as "0100-00-000". I used LEFT formula to return the first 4 digit. Then I used RIGHT formula on the same cell to return the last 3 digit but somehow the formula doesn't work. It won't give me a error message but just keep my formula in the cell. Please help and thanks |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
LEFT &RIGHT formula on the same cell
If you are not getting an error message, and you can see the formula after
entered, then you probably have the cell formatted as text. Format it as general, and then re-edit the cell (select the cell, hit F2, hit enter). Also, you can try this formula. It will remove all "-" from the text string. =SUBSTITUTE(A1,"-","") "Bonnie W." wrote: I was trying to take off hyphen within a text string, such as "0100-00-000". I used LEFT formula to return the first 4 digit. Then I used RIGHT formula on the same cell to return the last 3 digit but somehow the formula doesn't work. It won't give me a error message but just keep my formula in the cell. Please help and thanks |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
LEFT &RIGHT formula on the same cell
"Bonnie W." <Bonnie wrote in message
... I was trying to take off hyphen within a text string, such as "0100-00-000". I used LEFT formula to return the first 4 digit. Then I used RIGHT formula on the same cell to return the last 3 digit but somehow the formula doesn't work. It won't give me a error message but just keep my formula in the cell. If the cell is formatted as 'text' then you'll need to change it to 'general' and then edit the formula to refresh it. If you just want to remove the dashes from the string then try =SUBSTITUTE(A1,"-","") This returns the value as a string. If you want it to be interpreted as a number then add two minus signs like this =--SUBSTITUTE(A1,"-","") |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
LEFT &RIGHT formula on the same cell
If all you want to do is eliminate the hyphens, you can do that with the Edit
Replace Replace hyphen with nothing (leave blank) Replace all Vaya con Dios, Chuck, CABGx3 "Bonnie W." wrote: I was trying to take off hyphen within a text string, such as "0100-00-000". I used LEFT formula to return the first 4 digit. Then I used RIGHT formula on the same cell to return the last 3 digit but somehow the formula doesn't work. It won't give me a error message but just keep my formula in the cell. Please help and thanks |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formula Problem - interrupted by #VALUE! in other cells!? | Excel Worksheet Functions | |||
How do I set a cell value based on a formula in another cell? | Excel Discussion (Misc queries) | |||
Possible Lookup Table | Excel Worksheet Functions | |||
adding a formula in a cell but when cell = 0 cell is blank | Excel Worksheet Functions | |||
Excel VB-Copy formula down until adjacent cell (left) is blank? | Excel Discussion (Misc queries) |