Rearranging text

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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)
 
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 said:
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 said:
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
Click to expand...
Click to expand...
 
=IF(ISNUMBER(A1),A1,"")

(Bob missed a closing paren.)
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 said:
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 said:
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
Click to expand...
Click to expand...
Click to expand...
 
Oops, put this in A1:
=IF(ISNUMBER(A2),A2,"")


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 said:
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 said:
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
Click to expand...
Click to expand...
Click to expand...
 
Sigh.

Put this in B1:
=IF(ISNUMBER(A2),A2,"")


Dave said:
Oops, put this in A1:
=IF(ISNUMBER(A2),A2,"")
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 said:
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)



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
Click to expand...
Click to expand...
Click to expand...
 
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 said:
Sigh.

Put this in B1:
=IF(ISNUMBER(A2),A2,"")


Dave said:
Oops, put this in A1:
=IF(ISNUMBER(A2),A2,"")
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.

:

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)



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


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 said:
Sigh.

Put this in B1:
=IF(ISNUMBER(A2),A2,"")


Dave said:
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.

:

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)



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
Click to expand...
Click to expand...
Click to expand...
 
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 said:
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)


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 said:
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.

:

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)



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
Click to expand...
Click to expand...
Click to expand...
 
Didn't the autofilter technique work?
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 said:
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)


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.

:

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.

:

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)



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
Click to expand...
Click to expand...
Click to expand...
 
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 said:
Didn't the autofilter technique work?
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 said:
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.

:

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.

:

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)



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
Click to expand...
Click to expand...
Click to expand...
 
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.
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 said:
Didn't the autofilter technique work?
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.

:

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.

:

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.

:

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)



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
Click to expand...
Click to expand...
Click to expand...
 
Yes that worked I was filtering on column A.
Thanks so much. Sorry for so many questions.

Dave Peterson said:
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.
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 said:
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.

:

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.

:

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.

:

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)



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
Click to expand...
Click to expand...
Click to expand...
 
Not a problem.
(It took me 3 times to get the formula right <vbg>.)
Yes that worked I was filtering on column A.
Thanks so much. Sorry for so many questions.

Dave Peterson said:
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.
Yes the auto filter works for the blanks but I can't figure out how to remove
the rows with the $ amounts. Any ideas.

:

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.

:

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.

:

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.

:

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)



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