Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 2
Default Separating characters from a cell so I can sort on them

I've got data that includes a variable. Either -B or -P. I need to sort on
this variable.
348.00-P
113.20-P
2023.90-B
411.00-B
504.00-B
135.00-P

Simple explaination, please. I don't really speak Excel language very well...
Thanks
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1,388
Default Separating characters from a cell so I can sort on them

Hi Princess,
One simple way is with a helper column.
If your data is in Column A, stating A2, then in B2 enter:
=RIGHT(A2,1)
Copy down to the last row of your data in Column A.
Select all data in Columns A and B:
Data Sort:
Sort By: Column B.
OK

Regards - Dave.

"Princess Caroline" wrote:

I've got data that includes a variable. Either -B or -P. I need to sort on
this variable.
348.00-P
113.20-P
2023.90-B
411.00-B
504.00-B
135.00-P

Simple explaination, please. I don't really speak Excel language very well...
Thanks

  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3,572
Default Separating characters from a cell so I can sort on them

You can create a "helper" column, where you extract the last letter, and
then sort both columns together, using the helper column as the sort key.

Say your data was in Column A.

In B1 enter this formula:

=Right(A1)

And then copy down as needed.

You might try *double* clicking on the fill handle (little black square in
the lower right corner of a *selected* cell).

This will *automatically* copy the formula in B1 down Column B, as far as
there is data in Column A.

Now select both columns, and sort on Column B.


--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Princess Caroline" wrote in
message ...
I've got data that includes a variable. Either -B or -P. I need to sort
on
this variable.
348.00-P
113.20-P
2023.90-B
411.00-B
504.00-B
135.00-P

Simple explaination, please. I don't really speak Excel language very
well...
Thanks



  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 2
Default Separating characters from a cell so I can sort on them

Ok, I got this to work. Wonder if I could actually remove the -B or -P from
the number so I can total the number?

"RagDyer" wrote:

You can create a "helper" column, where you extract the last letter, and
then sort both columns together, using the helper column as the sort key.

Say your data was in Column A.

In B1 enter this formula:

=Right(A1)

And then copy down as needed.

You might try *double* clicking on the fill handle (little black square in
the lower right corner of a *selected* cell).

This will *automatically* copy the formula in B1 down Column B, as far as
there is data in Column A.

Now select both columns, and sort on Column B.


--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Princess Caroline" wrote in
message ...
I've got data that includes a variable. Either -B or -P. I need to sort
on
this variable.
348.00-P
113.20-P
2023.90-B
411.00-B
504.00-B
135.00-P

Simple explaination, please. I don't really speak Excel language very
well...
Thanks




  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3,572
Default Separating characters from a cell so I can sort on them

You can kill 2 birds with one stone using TTC (Text To Columns).

Select the column of data in Column A, then, from the Menu Bar:

<Data <TextToColumns <Delimited <Next

On the 2nd page of the TTC wizard, click on "Other",
And key in the dash, in the next box.

If you look down in the "Data Preview" window, you'll see the way the data
will be separated.

Now, just click <Finish.

You should have your first column in a calculable format, and the second
column ready to be used as the sort key.
--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Princess Caroline" wrote in
message ...
Ok, I got this to work. Wonder if I could actually remove the -B or -P
from
the number so I can total the number?

"RagDyer" wrote:

You can create a "helper" column, where you extract the last letter, and
then sort both columns together, using the helper column as the sort key.

Say your data was in Column A.

In B1 enter this formula:

=Right(A1)

And then copy down as needed.

You might try *double* clicking on the fill handle (little black square
in
the lower right corner of a *selected* cell).

This will *automatically* copy the formula in B1 down Column B, as far as
there is data in Column A.

Now select both columns, and sort on Column B.


--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Princess Caroline" wrote in
message ...
I've got data that includes a variable. Either -B or -P. I need to
sort
on
this variable.
348.00-P
113.20-P
2023.90-B
411.00-B
504.00-B
135.00-P

Simple explaination, please. I don't really speak Excel language very
well...
Thanks








  #6   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 123
Default Separating characters from a cell so I can sort on them

If each value will have a hyphen that separates the number from the
last character(s) you can use:
=VALUE(LEFT(B5,FIND("-",B5)-1))

The FIND function locates the hyphen
The LEFT function collects the characters before that (the -1 ensure
that you don't get the hyphen also)
The VALUE function converts the result to a value to allow you to
perform your math

On Jun 9, 2:59 pm, Princess Caroline
wrote:
Ok, I got this to work. Wonder if I could actually remove the -B or -P from
the number so I can total the number?

"RagDyer" wrote:
You can create a "helper" column, where you extract the last letter, and
then sort both columns together, using the helper column as the sort key.


Say your data was in Column A.


In B1 enter this formula:


=Right(A1)


And then copy down as needed.


You might try *double* clicking on the fill handle (little black square in
the lower right corner of a *selected* cell).


This will *automatically* copy the formula in B1 down Column B, as far as
there is data in Column A.


Now select both columns, and sort on Column B.


--
HTH,


RD


---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Princess Caroline" wrote in
...
I've got data that includes a variable. Either -B or -P. I need to sort
on
this variable.
348.00-P
113.20-P
2023.90-B
411.00-B
504.00-B
135.00-P


Simple explaination, please. I don't really speak Excel language very
well...
Thanks


  #7   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1
Default Separating characters from a cell so I can sort on them

Assuming your data is in Column A starting in Row 1, you can get your total
using this array-entered** formula...

=SUM(IF(A1:A1000<"",--LEFT(A1:A1000,FIND("-",A1:A1000)-1),0))

You can change the maximum possible row number from the 1000 I used to
whatever value you want.

** NOTE...
Commit the above function using Ctrl+Shift+Enter and NOT just Enter by
itself.

Rick


"Princess Caroline" wrote in
message ...
Ok, I got this to work. Wonder if I could actually remove the -B or -P
from
the number so I can total the number?

"RagDyer" wrote:

You can create a "helper" column, where you extract the last letter, and
then sort both columns together, using the helper column as the sort key.

Say your data was in Column A.

In B1 enter this formula:

=Right(A1)

And then copy down as needed.

You might try *double* clicking on the fill handle (little black square
in
the lower right corner of a *selected* cell).

This will *automatically* copy the formula in B1 down Column B, as far as
there is data in Column A.

Now select both columns, and sort on Column B.


--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Princess Caroline" wrote in
message ...
I've got data that includes a variable. Either -B or -P. I need to
sort
on
this variable.
348.00-P
113.20-P
2023.90-B
411.00-B
504.00-B
135.00-P

Simple explaination, please. I don't really speak Excel language very
well...
Thanks





Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Separating text in a cell Aaron Excel Worksheet Functions 2 February 11th 08 01:55 AM
Separating Names in a Cell Motaad Excel Discussion (Misc queries) 3 August 17th 06 04:26 PM
Separating Names in a cell Motaad Excel Discussion (Misc queries) 1 August 16th 06 09:29 PM
How do I sort in Excel by the no. of characters in a cell? yasmingeo Excel Worksheet Functions 3 October 21st 05 12:29 PM
Separating data in a cell chellegar Excel Discussion (Misc queries) 4 August 16th 05 08:57 PM


All times are GMT +1. The time now is 06:08 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"