Format Cells - Custom

**verbazon** · 01-20-2010, 04:34 PM

Hello all,

One of the columns in my spreadsheet has cells containing both numeric and alphanumeric data

Example:
12345678
ABC45678

The column needs to be formatted as follows:

123456.78
ABC456.78

I've tried many custom formats for this formatting to work, including ######.## and 000000.00 but the formatting does not seems to be working for the alphanumeric data cells.

Any help is greatly appreciated, thank you.

**NBVC** · 01-20-2010, 04:50 PM

Try in another column...=Replace(A1,7,0,".") and copy down, where A1 contains first value to format..

Then copy the new column and Edit|Paste Special Values over the original and delete the formula column.

**rwgrietveld** · 01-20-2010, 04:53 PM

I think you can't, as in the formatting alone, you can't split strings

Pos;Neg;zero;text

Text placement is done with @, but I can't think of a possible method like Left(@,len(@)-2).right(@,2)

**verbazon** · 01-20-2010, 05:24 PM

i have multiple cells in this column that needs this formatting.. how could i use this to account for all 200+ cells that need this formatting of 2 decimal places, not just one cell particularly? THANK YOU!

**NBVC** · 01-20-2010, 05:26 PM

The formula I provided can be copied down the column the A1 will change to reference A2, A3, etc as you copy it down...

To copy down, select the cell with the first formula and select and drag the little black square at bottom right corner of cell down to the bottom of the column...