|
Divide by 3
(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). |
Divide by 3
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 |
Divide by 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? |
Divide by 3
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 |
Divide by 3
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 |
Divide by 3
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 |
Divide by 3
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 |
Divide by 3
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 |
Divide by 3
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 |
Divide by 3
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 |
Divide by 3
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 |
Divide by 3
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 |
Divide by 3
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 |
Divide by 3
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 |
Divide by 3
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 |
| All times are GMT +1. The time now is 07:57 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com