Divide by 3

G

GARY

(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).
 
B

Bryan Hessey

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

--
 
G

GARY

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

Bryan Hessey

try

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

--
 
B

Bryan Hessey

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

--
 
G

GARY

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
 
G

GARY

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
 
B

Bryan Hessey

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

Bryan Hessey

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

--
 
G

GARY

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
 
B

Bryan Hessey

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)

--
 
B

Bryan Hessey

As per the attached

File:
http://www.excelforum.com/attachment.php?attachmentid=4741&d=1147246269

--

Bryan said:
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)

--
Click to expand...


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

GARY

Hi Bryan,

It works!!!

I discovered my problem. Thread #5 contains:

Bryan said:
try
Click to expand...
=IF(MID(A1,2,1)="$",LEFT(A1,1)&TEXT(MID(A1,3,FIND("
",A1)-3)/3,"$#.00")&MID(A1,FIND(" ",A1),99),A1)
Click to expand...

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
 
B

Bryan Hessey

Good to see, and thanks for the response.

--
Hi Bryan,

It works!!!

I discovered my problem. Thread #5 contains:




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
Click to expand...
 
Top