Number Format

[ricobrogna]

I am wondering how I can format:
$985k

To:
$0.985

Without just simply typing it since I have hundreds of cells to reformat with slightly different numbers.I don't know how to remove the k and I don't know how to make the 985 become .985

Thanks

[udprocks]

Hi Rico,

first thing in this have u manually written 985k..?

[romperstomper]

You could use Find & Replace (Ctrl + H):
Replace "k" with 000
Replace $ with nothing (leave the replace box blank)
Then format the cells using a custom number format of:
#,##0.000,,
for example.

[prjt]

...If its all by the same factor (aka 1,000,000) then couldn't you just insert a column with the formula "=*cell with first number*/1000000" and then double click bottom right corner to fill down. problem solved? Occam's Razor?

[newdoverman]

This custom format will produce the formatting that you want

$0\.000

You can use Search and replace to get just the numbers or you can use a formula like:

Please Login or Register  to view this content.

Result:

	A	B	C	D	E	F
1	Original	Seach and Replace	Formatting applied
2	$985K	985	$0.985
3	$456K	456	$0.456
4	$982K	982	$0.982
5	$1234K	1234	$1.234
6	985K	985	$0.985
7
8		Use Formula
9	$985K	$0.985	=SUBSTITUTE(SUBSTITUTE(A8,"$",""),"K","")*1
10	$456K	$0.456
11	$982K	$0.982
12	$1234K	$1.234
13	985K	$0.985
14
15	Custom cell formatting		$0\.000