How do I truncate something like this?
 

I have a column with text like

BRAND X - version 1
BRAND X - version 2.4
BRAND Y - version 3D
BRAND Y - version 8C
BRAND Y - version 3.5.6
BRAND Z - version 5X

and so on. It's very long spreadsheet. I'd like to chop off the
version info (which is of varying length) and group everything
together by just the brand name. How do I do this?

Thanks for any input.

How do I truncate something like this?
 

Assuming that the divider between the Brand name and the version number is
always " - " (and that " - " doesn't show up in a brand name), then a formula
like this will work. Assumes first entry is in A1, so in B1 you could put:
=Left(A1,Find(" - ",A1)-1)

If you want to then be able to do away with the combined Brand - Version
information in the original column, Copy and Paste Special | Values the
Brand-only information. That will convert them to true values, not the
results of a formula and you will no longer need the source data in column A.

"ckeys" wrote:

I have a column with text like

BRAND X - version 1
BRAND X - version 2.4
BRAND Y - version 3D
BRAND Y - version 8C
BRAND Y - version 3.5.6
BRAND Z - version 5X

and so on. It's very long spreadsheet. I'd like to chop off the
version info (which is of varying length) and group everything
together by just the brand name. How do I do this?

Thanks for any input.

How do I truncate something like this?
 

Maybe you can select the range
edit|replace
what: _-* (spacebar, hyphen, asterisk)
with: (leave blank)
replace all

Then you could use a pivottable or sort your data and use data|subtotals.

ckeys wrote:

I have a column with text like

BRAND X - version 1
BRAND X - version 2.4
BRAND Y - version 3D
BRAND Y - version 8C
BRAND Y - version 3.5.6
BRAND Z - version 5X

and so on. It's very long spreadsheet. I'd like to chop off the
version info (which is of varying length) and group everything
together by just the brand name. How do I do this?

Thanks for any input.

--

Dave Peterson

How do I truncate something like this?
 

Data/ Text to Columns/ Delimited/ Other: select hyphen as delimiter.
--
David Biddulph

"ckeys" wrote in message
...
I have a column with text like

BRAND X - version 1
BRAND X - version 2.4
BRAND Y - version 3D
BRAND Y - version 8C
BRAND Y - version 3.5.6
BRAND Z - version 5X

and so on. It's very long spreadsheet. I'd like to chop off the
version info (which is of varying length) and group everything
together by just the brand name. How do I do this?

Thanks for any input.

How do I truncate something like this?
 

You could try DataText to ColumnsDelimited by "-"


Gord Dibben MS Excel MVP

On Sat, 30 Dec 2006 09:20:50 -0600, ckeys wrote:

I have a column with text like

BRAND X - version 1
BRAND X - version 2.4
BRAND Y - version 3D
BRAND Y - version 8C
BRAND Y - version 3.5.6
BRAND Z - version 5X

and so on. It's very long spreadsheet. I'd like to chop off the
version info (which is of varying length) and group everything
together by just the brand name. How do I do this?

Thanks for any input.

How do I truncate something like this?
 

You can try TTC (Text To Columns).

Select your column of data, then:
<Data <Text To Columns <Delimited <Next,

Click in "Other" and enter the dash " - ", then <Next.

In the "Data Preview" window, click in the second column containing the
versions to select it.

Then click on "Do Not Import Column".
That column header changes to "Skip".

Click <Finish, and you've eliminated all the versions data.
--

HTH,

RD
================================================== ===
Please keep all correspondence within the Group, so all may benefit!
================================================== ===

"ckeys" wrote in message
...
I have a column with text like

BRAND X - version 1
BRAND X - version 2.4
BRAND Y - version 3D
BRAND Y - version 8C
BRAND Y - version 3.5.6
BRAND Z - version 5X

and so on. It's very long spreadsheet. I'd like to chop off the
version info (which is of varying length) and group everything
together by just the brand name. How do I do this?

Thanks for any input.

How do I truncate something like this?
 

On Sat, 30 Dec 2006 10:12:37 -0600, Dave Peterson
wrote:

Maybe you can select the range
edit|replace
what: _-* (spacebar, hyphen, asterisk)
with: (leave blank)
replace all

Thanks for a great and simple solution. I had no idea that I could use
wildcards in Excel searches... (Must be a fine print in my Excel
books)

Thanks to all others for wonderful suggestions. I've learned a lot
from this group.