| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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. |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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. |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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. |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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. |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Trying to truncate or separate the first 3 characters/digits of co | Excel Discussion (Misc queries) | |||
| truncate | Excel Worksheet Functions | |||
| truncate ssn to first four digits | Excel Discussion (Misc queries) | |||
| I WANT to truncate my leading zeros | Excel Discussion (Misc queries) | |||
| Truncate scientific notation | Excel Worksheet Functions | |||
Hybrid Mode
