Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Text
In Cell A1,2 ,3 ect i have the following info
12345BO 1234BO 123456BO How do i convert these in Cells B1, 2, 3 etc to : 012345 001234 123456 To create a 6 figure number with the BO dropped off? |
#2
|
|||
|
|||
in column b put this formula
find(a1,"BO",1) in column c put this formula mid(a1,1,b1-1) format the c column to numbers "Dava Sutts" wrote: In Cell A1,2 ,3 ect i have the following info 12345BO 1234BO 123456BO How do i convert these in Cells B1, 2, 3 etc to : 012345 001234 123456 To create a 6 figure number with the BO dropped off? |
#3
|
|||
|
|||
One way (if you don't need the letters at all anymore):
Make ure there are blank cells to the right of thses cells. Select the cells Go to Data | Text to Columns Choose Delimited and click Next Set B as the Delimiter Click Finish You'll end up with the Numbers in their original cell, the B will be gone and the O will be in a cell to the right. You can delete the extraneous O's. Then, with your number cells Selected: Format | Cells On the Number Tab Choose Custom Type 6 consecutive zeroes (000000) in the line provided. Don't use the parentheses. This will give you the number format you typed out below. tj "Dava Sutts" wrote: In Cell A1,2 ,3 ect i have the following info 12345BO 1234BO 123456BO How do i convert these in Cells B1, 2, 3 etc to : 012345 001234 123456 To create a 6 figure number with the BO dropped off? |
#4
|
|||
|
|||
Thanks, you have saved me a load of time.
"tjtjjtjt" wrote: One way (if you don't need the letters at all anymore): Make ure there are blank cells to the right of thses cells. Select the cells Go to Data | Text to Columns Choose Delimited and click Next Set B as the Delimiter Click Finish You'll end up with the Numbers in their original cell, the B will be gone and the O will be in a cell to the right. You can delete the extraneous O's. Then, with your number cells Selected: Format | Cells On the Number Tab Choose Custom Type 6 consecutive zeroes (000000) in the line provided. Don't use the parentheses. This will give you the number format you typed out below. tj "Dava Sutts" wrote: In Cell A1,2 ,3 ect i have the following info 12345BO 1234BO 123456BO How do i convert these in Cells B1, 2, 3 etc to : 012345 001234 123456 To create a 6 figure number with the BO dropped off? |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Text
hi
I have the same issue where I need 4, 12 to be displayed as 004 & 012. I did Format | Cells On the Number Tab Choose Custom Type 3 consecutive zeroes (000). the problem is that when i click on the individual cells, it shows 4 & 12, even though it displays on the ws as 004 & 012. How can I format these into numbers, where the individual cells also shows 004 & 012. I also tried the text (A1, "000), which works but stores them as text and I need them to be formated to numbers. -- Thanks "tjtjjtjt" wrote: One way (if you don't need the letters at all anymore): Make ure there are blank cells to the right of thses cells. Select the cells Go to Data | Text to Columns Choose Delimited and click Next Set B as the Delimiter Click Finish You'll end up with the Numbers in their original cell, the B will be gone and the O will be in a cell to the right. You can delete the extraneous O's. Then, with your number cells Selected: Format | Cells On the Number Tab Choose Custom Type 6 consecutive zeroes (000000) in the line provided. Don't use the parentheses. This will give you the number format you typed out below. tj "Dava Sutts" wrote: In Cell A1,2 ,3 ect i have the following info 12345BO 1234BO 123456BO How do i convert these in Cells B1, 2, 3 etc to : 012345 001234 123456 To create a 6 figure number with the BO dropped off? |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Text
Formatting is simply that............formatting.
Does not change the underlying value of the data. What you see in the formula bar is the real value. What you see in the cell is the formatted display. If you want to see 004 and 012 in the formula bar, you must precede the data with an apostrophe. Which makes it text. Why do you feel you need to see 004 and 012 in formula bar? Gord Dibben MS Excel MVP On Tue, 25 Aug 2009 10:57:04 -0700, user wrote: hi I have the same issue where I need 4, 12 to be displayed as 004 & 012. I did Format | Cells On the Number Tab Choose Custom Type 3 consecutive zeroes (000). the problem is that when i click on the individual cells, it shows 4 & 12, even though it displays on the ws as 004 & 012. How can I format these into numbers, where the individual cells also shows 004 & 012. I also tried the text (A1, "000), which works but stores them as text and I need them to be formated to numbers. |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Text
If you want to see the leading 0's in the formulabar (or while editing within
the cell), then you'll have to either pre-format the cell as Text or start your entry with an apostrophe. If you want to keep the values numeric, then you'll have to force yourself not to look at the formulabar. user wrote: hi I have the same issue where I need 4, 12 to be displayed as 004 & 012. I did Format | Cells On the Number Tab Choose Custom Type 3 consecutive zeroes (000). the problem is that when i click on the individual cells, it shows 4 & 12, even though it displays on the ws as 004 & 012. How can I format these into numbers, where the individual cells also shows 004 & 012. I also tried the text (A1, "000), which works but stores them as text and I need them to be formated to numbers. -- Thanks "tjtjjtjt" wrote: One way (if you don't need the letters at all anymore): Make ure there are blank cells to the right of thses cells. Select the cells Go to Data | Text to Columns Choose Delimited and click Next Set B as the Delimiter Click Finish You'll end up with the Numbers in their original cell, the B will be gone and the O will be in a cell to the right. You can delete the extraneous O's. Then, with your number cells Selected: Format | Cells On the Number Tab Choose Custom Type 6 consecutive zeroes (000000) in the line provided. Don't use the parentheses. This will give you the number format you typed out below. tj "Dava Sutts" wrote: In Cell A1,2 ,3 ect i have the following info 12345BO 1234BO 123456BO How do i convert these in Cells B1, 2, 3 etc to : 012345 001234 123456 To create a 6 figure number with the BO dropped off? -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Import comma delimited text | Excel Discussion (Misc queries) | |||
How do I rotate a text box | Charts and Charting in Excel | |||
I enter numbers and they are stored as text | Excel Discussion (Misc queries) | |||
Text Boxes | Excel Discussion (Misc queries) | |||
Text Cuts Off | Excel Discussion (Misc queries) |