| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Formatting cells with numbers and text
I have a column of numbers with text; i.e., 23L252350, 06K100997, 50J304888,
etc. These numbers and text need to have a hyphen inserted after the first two numbers/characters and the next four numbers/characters, i.e., 23-L252-350, 06-K100-997, 50-J304-888. I tried using Format-Cells-Number-Custom, but cannot seem to get it to work since the 3rd character is usually (but not always) text. Any assistance would be greatly appreciated. Thank you. |
|
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
Formatting cells with numbers and text
Hi,
try =LEFT(B7,2)&"-"&MID(B7,3,4)&"-"&RIGHT(B7,3) change the cell # to fit your needs "Dazed and Confused" wrote: I have a column of numbers with text; i.e., 23L252350, 06K100997, 50J304888, etc. These numbers and text need to have a hyphen inserted after the first two numbers/characters and the next four numbers/characters, i.e., 23-L252-350, 06-K100-997, 50-J304-888. I tried using Format-Cells-Number-Custom, but cannot seem to get it to work since the 3rd character is usually (but not always) text. Any assistance would be greatly appreciated. Thank you. |
|
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
Formatting cells with numbers and text
Sorry, add the formula in a column beside where you have the information
"Eduardo" wrote: Hi, try =LEFT(B7,2)&"-"&MID(B7,3,4)&"-"&RIGHT(B7,3) change the cell # to fit your needs "Dazed and Confused" wrote: I have a column of numbers with text; i.e., 23L252350, 06K100997, 50J304888, etc. These numbers and text need to have a hyphen inserted after the first two numbers/characters and the next four numbers/characters, i.e., 23-L252-350, 06-K100-997, 50-J304-888. I tried using Format-Cells-Number-Custom, but cannot seem to get it to work since the 3rd character is usually (but not always) text. Any assistance would be greatly appreciated. Thank you. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Formatting cells for text and numbers | Excel Worksheet Functions | |||
| Formatting cells for text and numbers | Excel Worksheet Functions | |||
| Formula Text String: Formatting Text and Numbers? | Excel Discussion (Misc queries) | |||
| formatting cells with numbers as text | Excel Discussion (Misc queries) | |||
| numbers to text with formatting | Excel Discussion (Misc queries) | |||
![Dazed and Confused[_2_] is offline](https://www.excelbanter.com/images/SultanThemeVB3R/statusicon/user_offline.gif)
Linear Mode
