Text

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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?
 
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
 
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
 
Thanks, you have saved me a load of time.

tjtjjtjt said:
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
Click to expand...
 
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).
Click to expand...
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.
 
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
 
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.
 
Back
Top