Try

=IF(MID(A1,2,1)="$",LEFT(A1,1)&TEXT(MID(A1,3,FIND( "
",MID(A1,2,99))-2)/3,"$#.00")&MID(A1,FIND(" ",MID(A1,2,99))+1,99),A1)

for the correct format on the figure

--


GARY Wrote:
Hi Bryan,

(Note: There are no blank lines between the rows my spreadsheet).


The cells in column A contain:

4363 2ND ST RIVERSIDE

NO 170 DEFAULT 219163005 219163005-7

$5,779.92 BRIONES, ANDREW L & ESTELITA A

NO 171 DEFAULT 221100002 221100002-6

$989.46 MOLSON, KATHERINE

NO 172 DEFAULT 223030019 223030019-0

$1,558.62 TEN FOUR CORP

NO 173 DEFAULT 223030025 223030025-5

$2,236.56 TEN FOUR CORP

NO 174 DEFAULT 225233001 225233001-4

$3,571.80 TAVAGLIONE, NANCY E




Your latest formula results in the following in the cells in Column B

4363 2ND ST RIVERSIDE

NO 170 DEFAULT 219163005 219163005-7

#VALUE!
NO 171 DEFAULT 221100002 221100002-6

#VALUE!
NO 172 DEFAULT 223030019 223030019-0

#VALUE!
NO 173 DEFAULT 223030025 223030025-5

#VALUE!
NO 174 DEFAULT 225233001 225233001-4

#VALUE!



But they should contain:

4363 2ND ST RIVERSIDE

NO 170 DEFAULT 219163005 219163005-7

$1,926.64 BRIONES, ANDREW L & ESTELITA A

NO 171 DEFAULT 221100002 221100002-6

$329.82 MOLSON, KATHERINE

NO 172 DEFAULT 223030019 223030019-0

$519.54 TEN FOUR CORP

NO 173 DEFAULT 223030025 223030025-5

$745.52 TEN FOUR CORP

NO 174 DEFAULT 225233001 225233001-4

$1,190.60 TAVAGLIONE, NANCY E


--
Bryan Hessey
------------------------------------------------------------------------
Bryan Hessey's Profile: http://www.excelforum.com/member.php...o&userid=21059
View this thread: http://www.excelforum.com/showthread...hreadid=540474