How do I truncate something like this?

C

ckeys

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.
 
J

JLatham

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.
 
D

Dave Peterson

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.
 
R

RagDyeR

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!
=====================================================

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.
 
Top