| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Rearranging text
Sample text
TEST123 $100 TEST124 $200 TEST125 $300 How can I put the dollar amount values in a separate column [column b] on the same line as text. Thanks |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Rearranging text
In B2, add
=IF(ISNUMBER(A3,A3,"") and copy down. -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "falcios" wrote in message ... Sample text TEST123 $100 TEST124 $200 TEST125 $300 How can I put the dollar amount values in a separate column [column b] on the same line as text. Thanks |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Rearranging text
Thanks Bob
I copied the function on B2 and I got an error. I am not familiar with this function. If my text starts in cell A1 what should the function line read. "Bob Phillips" wrote: In B2, add =IF(ISNUMBER(A3,A3,"") and copy down. -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "falcios" wrote in message ... Sample text TEST123 $100 TEST124 $200 TEST125 $300 How can I put the dollar amount values in a separate column [column b] on the same line as text. Thanks |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Rearranging text
=IF(ISNUMBER(A1),A1,"")
(Bob missed a closing paren.) falcios wrote: Thanks Bob I copied the function on B2 and I got an error. I am not familiar with this function. If my text starts in cell A1 what should the function line read. "Bob Phillips" wrote: In B2, add =IF(ISNUMBER(A3,A3,"") and copy down. -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "falcios" wrote in message ... Sample text TEST123 $100 TEST124 $200 TEST125 $300 How can I put the dollar amount values in a separate column [column b] on the same line as text. Thanks -- Dave Peterson |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Rearranging text
Oops, put this in A1:
=IF(ISNUMBER(A2),A2,"") falcios wrote: Thanks Bob I copied the function on B2 and I got an error. I am not familiar with this function. If my text starts in cell A1 what should the function line read. "Bob Phillips" wrote: In B2, add =IF(ISNUMBER(A3,A3,"") and copy down. -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "falcios" wrote in message ... Sample text TEST123 $100 TEST124 $200 TEST125 $300 How can I put the dollar amount values in a separate column [column b] on the same line as text. Thanks -- Dave Peterson |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Rearranging text
Sigh.
Put this in B1: =IF(ISNUMBER(A2),A2,"") Dave Peterson wrote: Oops, put this in A1: =IF(ISNUMBER(A2),A2,"") falcios wrote: Thanks Bob I copied the function on B2 and I got an error. I am not familiar with this function. If my text starts in cell A1 what should the function line read. "Bob Phillips" wrote: In B2, add =IF(ISNUMBER(A3,A3,"") and copy down. -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "falcios" wrote in message ... Sample text TEST123 $100 TEST124 $200 TEST125 $300 How can I put the dollar amount values in a separate column [column b] on the same line as text. Thanks -- Dave Peterson -- Dave Peterson |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Rearranging text
Thanks Dave it worked.
Sorry but I have another question. Now I have duplicate numbers in my worksheet. How I do remove the rows with the numbers under the text starting at A2. Thanks. "Dave Peterson" wrote: Sigh. Put this in B1: =IF(ISNUMBER(A2),A2,"") Dave Peterson wrote: Oops, put this in A1: =IF(ISNUMBER(A2),A2,"") falcios wrote: Thanks Bob I copied the function on B2 and I got an error. I am not familiar with this function. If my text starts in cell A1 what should the function line read. "Bob Phillips" wrote: In B2, add =IF(ISNUMBER(A3,A3,"") and copy down. -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "falcios" wrote in message ... Sample text TEST123 $100 TEST124 $200 TEST125 $300 How can I put the dollar amount values in a separate column [column b] on the same line as text. Thanks -- Dave Peterson -- Dave Peterson |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Rearranging text
Convert column B to values
Select column B edit|copy edit|paste special|values Now apply Data|Filter|autofilter to that column Filter to show the cells that look blank Delete those visible rows Then data|filter|autofilter (to remove that filter) falcios wrote: Thanks Dave it worked. Sorry but I have another question. Now I have duplicate numbers in my worksheet. How I do remove the rows with the numbers under the text starting at A2. Thanks. "Dave Peterson" wrote: Sigh. Put this in B1: =IF(ISNUMBER(A2),A2,"") Dave Peterson wrote: Oops, put this in A1: =IF(ISNUMBER(A2),A2,"") falcios wrote: Thanks Bob I copied the function on B2 and I got an error. I am not familiar with this function. If my text starts in cell A1 what should the function line read. "Bob Phillips" wrote: In B2, add =IF(ISNUMBER(A3,A3,"") and copy down. -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "falcios" wrote in message ... Sample text TEST123 $100 TEST124 $200 TEST125 $300 How can I put the dollar amount values in a separate column [column b] on the same line as text. Thanks -- Dave Peterson -- Dave Peterson -- Dave Peterson |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Rearranging text
This is how the spreadsheet looks now:
TEST123 $100 $100 TEST124 $200 $200 TEST125 $300 $300 How do I remove the dollar amounts rows below the text. "Dave Peterson" wrote: Convert column B to values Select column B edit|copy edit|paste special|values Now apply Data|Filter|autofilter to that column Filter to show the cells that look blank Delete those visible rows Then data|filter|autofilter (to remove that filter) falcios wrote: Thanks Dave it worked. Sorry but I have another question. Now I have duplicate numbers in my worksheet. How I do remove the rows with the numbers under the text starting at A2. Thanks. "Dave Peterson" wrote: Sigh. Put this in B1: =IF(ISNUMBER(A2),A2,"") Dave Peterson wrote: Oops, put this in A1: =IF(ISNUMBER(A2),A2,"") falcios wrote: Thanks Bob I copied the function on B2 and I got an error. I am not familiar with this function. If my text starts in cell A1 what should the function line read. "Bob Phillips" wrote: In B2, add =IF(ISNUMBER(A3,A3,"") and copy down. -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "falcios" wrote in message ... Sample text TEST123 $100 TEST124 $200 TEST125 $300 How can I put the dollar amount values in a separate column [column b] on the same line as text. Thanks -- Dave Peterson -- Dave Peterson -- Dave Peterson |
|
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Rearranging text
Didn't the autofilter technique work?
falcios wrote: This is how the spreadsheet looks now: TEST123 $100 $100 TEST124 $200 $200 TEST125 $300 $300 How do I remove the dollar amounts rows below the text. "Dave Peterson" wrote: Convert column B to values Select column B edit|copy edit|paste special|values Now apply Data|Filter|autofilter to that column Filter to show the cells that look blank Delete those visible rows Then data|filter|autofilter (to remove that filter) falcios wrote: Thanks Dave it worked. Sorry but I have another question. Now I have duplicate numbers in my worksheet. How I do remove the rows with the numbers under the text starting at A2. Thanks. "Dave Peterson" wrote: Sigh. Put this in B1: =IF(ISNUMBER(A2),A2,"") Dave Peterson wrote: Oops, put this in A1: =IF(ISNUMBER(A2),A2,"") falcios wrote: Thanks Bob I copied the function on B2 and I got an error. I am not familiar with this function. If my text starts in cell A1 what should the function line read. "Bob Phillips" wrote: In B2, add =IF(ISNUMBER(A3,A3,"") and copy down. -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "falcios" wrote in message ... Sample text TEST123 $100 TEST124 $200 TEST125 $300 How can I put the dollar amount values in a separate column [column b] on the same line as text. Thanks -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson |
|
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Rearranging text
Yes the auto filter works for the blanks but I can't figure out how to remove
the rows with the $ amounts. Any ideas. "Dave Peterson" wrote: Didn't the autofilter technique work? falcios wrote: This is how the spreadsheet looks now: TEST123 $100 $100 TEST124 $200 $200 TEST125 $300 $300 How do I remove the dollar amounts rows below the text. "Dave Peterson" wrote: Convert column B to values Select column B edit|copy edit|paste special|values Now apply Data|Filter|autofilter to that column Filter to show the cells that look blank Delete those visible rows Then data|filter|autofilter (to remove that filter) falcios wrote: Thanks Dave it worked. Sorry but I have another question. Now I have duplicate numbers in my worksheet. How I do remove the rows with the numbers under the text starting at A2. Thanks. "Dave Peterson" wrote: Sigh. Put this in B1: =IF(ISNUMBER(A2),A2,"") Dave Peterson wrote: Oops, put this in A1: =IF(ISNUMBER(A2),A2,"") falcios wrote: Thanks Bob I copied the function on B2 and I got an error. I am not familiar with this function. If my text starts in cell A1 what should the function line read. "Bob Phillips" wrote: In B2, add =IF(ISNUMBER(A3,A3,"") and copy down. -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "falcios" wrote in message ... Sample text TEST123 $100 TEST124 $200 TEST125 $300 How can I put the dollar amount values in a separate column [column b] on the same line as text. Thanks -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson |
|
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Rearranging text
Did you filter column B?
You can show the blanks there and get rid of both the rows that numbers in column A as well as rows with nothing in column A. falcios wrote: Yes the auto filter works for the blanks but I can't figure out how to remove the rows with the $ amounts. Any ideas. "Dave Peterson" wrote: Didn't the autofilter technique work? falcios wrote: This is how the spreadsheet looks now: TEST123 $100 $100 TEST124 $200 $200 TEST125 $300 $300 How do I remove the dollar amounts rows below the text. "Dave Peterson" wrote: Convert column B to values Select column B edit|copy edit|paste special|values Now apply Data|Filter|autofilter to that column Filter to show the cells that look blank Delete those visible rows Then data|filter|autofilter (to remove that filter) falcios wrote: Thanks Dave it worked. Sorry but I have another question. Now I have duplicate numbers in my worksheet. How I do remove the rows with the numbers under the text starting at A2. Thanks. "Dave Peterson" wrote: Sigh. Put this in B1: =IF(ISNUMBER(A2),A2,"") Dave Peterson wrote: Oops, put this in A1: =IF(ISNUMBER(A2),A2,"") falcios wrote: Thanks Bob I copied the function on B2 and I got an error. I am not familiar with this function. If my text starts in cell A1 what should the function line read. "Bob Phillips" wrote: In B2, add =IF(ISNUMBER(A3,A3,"") and copy down. -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "falcios" wrote in message ... Sample text TEST123 $100 TEST124 $200 TEST125 $300 How can I put the dollar amount values in a separate column [column b] on the same line as text. Thanks -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson |
|
#13
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Rearranging text
Yes that worked I was filtering on column A.
Thanks so much. Sorry for so many questions. "Dave Peterson" wrote: Did you filter column B? You can show the blanks there and get rid of both the rows that numbers in column A as well as rows with nothing in column A. falcios wrote: Yes the auto filter works for the blanks but I can't figure out how to remove the rows with the $ amounts. Any ideas. "Dave Peterson" wrote: Didn't the autofilter technique work? falcios wrote: This is how the spreadsheet looks now: TEST123 $100 $100 TEST124 $200 $200 TEST125 $300 $300 How do I remove the dollar amounts rows below the text. "Dave Peterson" wrote: Convert column B to values Select column B edit|copy edit|paste special|values Now apply Data|Filter|autofilter to that column Filter to show the cells that look blank Delete those visible rows Then data|filter|autofilter (to remove that filter) falcios wrote: Thanks Dave it worked. Sorry but I have another question. Now I have duplicate numbers in my worksheet. How I do remove the rows with the numbers under the text starting at A2. Thanks. "Dave Peterson" wrote: Sigh. Put this in B1: =IF(ISNUMBER(A2),A2,"") Dave Peterson wrote: Oops, put this in A1: =IF(ISNUMBER(A2),A2,"") falcios wrote: Thanks Bob I copied the function on B2 and I got an error. I am not familiar with this function. If my text starts in cell A1 what should the function line read. "Bob Phillips" wrote: In B2, add =IF(ISNUMBER(A3,A3,"") and copy down. -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "falcios" wrote in message ... Sample text TEST123 $100 TEST124 $200 TEST125 $300 How can I put the dollar amount values in a separate column [column b] on the same line as text. Thanks -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson |
|
#14
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Rearranging text
Not a problem.
(It took me 3 times to get the formula right <vbg.) falcios wrote: Yes that worked I was filtering on column A. Thanks so much. Sorry for so many questions. "Dave Peterson" wrote: Did you filter column B? You can show the blanks there and get rid of both the rows that numbers in column A as well as rows with nothing in column A. falcios wrote: Yes the auto filter works for the blanks but I can't figure out how to remove the rows with the $ amounts. Any ideas. "Dave Peterson" wrote: Didn't the autofilter technique work? falcios wrote: This is how the spreadsheet looks now: TEST123 $100 $100 TEST124 $200 $200 TEST125 $300 $300 How do I remove the dollar amounts rows below the text. "Dave Peterson" wrote: Convert column B to values Select column B edit|copy edit|paste special|values Now apply Data|Filter|autofilter to that column Filter to show the cells that look blank Delete those visible rows Then data|filter|autofilter (to remove that filter) falcios wrote: Thanks Dave it worked. Sorry but I have another question. Now I have duplicate numbers in my worksheet. How I do remove the rows with the numbers under the text starting at A2. Thanks. "Dave Peterson" wrote: Sigh. Put this in B1: =IF(ISNUMBER(A2),A2,"") Dave Peterson wrote: Oops, put this in A1: =IF(ISNUMBER(A2),A2,"") falcios wrote: Thanks Bob I copied the function on B2 and I got an error. I am not familiar with this function. If my text starts in cell A1 what should the function line read. "Bob Phillips" wrote: In B2, add =IF(ISNUMBER(A3,A3,"") and copy down. -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "falcios" wrote in message ... Sample text TEST123 $100 TEST124 $200 TEST125 $300 How can I put the dollar amount values in a separate column [column b] on the same line as text. Thanks -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Text entries behaving like numbers | Excel Discussion (Misc queries) | |||
| Aligning Wraped Text to the bottom of a cell | Excel Discussion (Misc queries) | |||
| Conversion to Text file format error | Excel Discussion (Misc queries) | |||
| How change dimensions of data label text box in pie chart? | Charts and Charting in Excel | |||
| Read Text File into Excel Using VBA | Excel Discussion (Misc queries) | |||
Linear Mode
