(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

Gary,

I had not expected a space as character one, new formula will follow

--

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

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)

--


Bryan Hessey Wrote:
Gary,

I had not expected a space as character one, new formula will follow

--


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

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

Hi Bryan,

Column A contains:

4363 2ND ST RIVERSIDE
NO 170 DEFAULT 219163005 219163005-7
$5,779.92 BRIONES, ANDREW L & ESTELITA A




In column B, your newest formula still results in:

4363 2ND ST RIVERSIDE
NO 170 DEFAULT 219163005 219163005-7
#VALUE!



Instead of

4363 2ND ST RIVERSIDE
NO 170 DEFAULT 219163005 219163005-7
$1,926.64 BRIONES, ANDREW L & ESTELITA A

Assuming that there is a space as the first character (which doesn't
always show on the display) then it works for me. Did you check the
formula for odd spaces that can be inserted into the formula?

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

--

GARY Wrote:
Hi Bryan,

Column A contains:

4363 2ND ST RIVERSIDE
NO 170 DEFAULT 219163005 219163005-7
$5,779.92 BRIONES, ANDREW L & ESTELITA A




In column B, your newest formula still results in:

4363 2ND ST RIVERSIDE
NO 170 DEFAULT 219163005 219163005-7
#VALUE!



Instead of

4363 2ND ST RIVERSIDE
NO 170 DEFAULT 219163005 219163005-7
$1,926.64 BRIONES, ANDREW L & ESTELITA A


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

As per the attached

File:
http://www.excelforum.com/attachment...1&d=1147246269

--

Bryan Hessey Wrote:
Assuming that there is a space as the first character (which doesn't
always show on the display) then it works for me. Did you check the
formula for odd spaces that can be inserted into the formula?

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

--


+-------------------------------------------------------------------+
|Filename: Book1.zip |
|Download: http://www.excelforum.com/attachment.php?postid=4741 |
+-------------------------------------------------------------------+

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

It works!!!

I discovered my problem. Thread #5 contains:

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)

When I copied that formula, I inadvertantly included the on the
second line so, when I pasted it into my spreadsheet, that section of
the formula looked like this:

FIND(" ",A1)-3)/3e

Deleting the resolved my problem.


Thank you muchly for you help!

gary

Good to see, and thanks for the response.

--

GARY Wrote:
Hi Bryan,

It works!!!

I discovered my problem. Thread #5 contains:

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)

When I copied that formula, I inadvertantly included the on the
second line so, when I pasted it into my spreadsheet, that section of
the formula looked like this:

FIND(" ",A1)-3)/3e

Deleting the resolved my problem.


Thank you muchly for you help!

gary


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