(I'm working with a .TXT file).

In column A, if the second character in the cell is an $ (for example:

$369.09 xxxxx
$4,200.24 xxxx
$38.67 xxxxx
$296,169.45 xxxxx
$13.68 xxxxx

is there a way to divide the amounts by 3? (For example:

$123.03 xxxxx
$1,400.08 xxxx
$12.89 xxxxx
$98,169.45 xxxxx
$4.56 xxxxx


(NOTE: If the second character in the row is NOT a $, leave the row as
is).

In the example shown the $ is the first character.

=IF(MID(A1,1,1)="$",MID(A1,2,FIND(" ",A1)-2)/3,"")

if the $ is the second charcter use

=IF(MID(A1,2,1)="$",MID(A1,3,FIND(" ",A1)-3)/3,"")

--

GARY Wrote:
(I'm working with a .TXT file).

In column A, if the second character in the cell is an $ (for example:

$369.09 xxxxx
$4,200.24 xxxx
$38.67 xxxxx
$296,169.45 xxxxx
$13.68 xxxxx

is there a way to divide the amounts by 3? (For example:

$123.03 xxxxx
$1,400.08 xxxx
$12.89 xxxxx
$98,169.45 xxxxx
$4.56 xxxxx


(NOTE: If the second character in the row is NOT a $, leave the row as
is).


--
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

The SECOND character is the $. It is followed by the amount and the
rest of the data.

What should your formula look like so the new cell contains the $, the
NEW amount (with comma and decimal point) plus the rest of the data?

try

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

--

GARY Wrote:
The SECOND character is the $. It is followed by the amount and the
rest of the data.

What should your formula look like so the new cell contains the $, the
NEW amount (with comma and decimal point) plus the rest of the data?


--
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

I should have added,

put the formula

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

in B1 and formula-drag down as far as your data,

Check that it looks ok, then select the column and Copy

Paste Special, Values back over itsself, then delete column A

re-save as .txt

Hope this helps

--

Bryan Hessey Wrote:
try

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

--


--
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

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

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

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