Divide by 3

  • Thread starter Thread starter GARY
  • Start date Start date
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).
 
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,"")

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

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

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

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

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Divide by 3 1
Excel 2002 : How to sum up items by month, date and year ? 4
Make command buttons remain static when form scrolls horizontally 2
Change characters in report 4
Excel 2002: Can I do conditional editing ? 3
multiple nested gridviews 0
Fixed Position for Groupings 3
Import txt file with fixed length from VBA 3

Back
Top