Splitting text and numbers from a cell

  • Thread starter Thread starter JimAA
  • Start date Start date
J

JimAA

Hi,
I'm trying to split a cell that contains a part number and a part
description into a cell that contains the part number and a cell that
contains the part description. There are about 7,000 cells that need to be
separated this way. I tried the text to columns wizard but that is very
labor intensive since some part numbers have spaces and letters in them.
Example:
124 TD45 88 Ceramic Insulators
12 671 6773 Copper Coils
116011 Variable Resistors

I got as far as the formula =MID(A1,1,FIND(" ",A!,1)-1)
but that only returns the first group of letters ("124", "12", "116011") how
can I extract "124 TD45 88", "12 671 6773", and "116011" into their own cells
and "Ceramic Insulators", etc. into their own cells?

Thanks for your help,
Jim
 
Hi,

This works for your posted examples but relies on wanting to extract the
last 2 words.

For the last 2 words and note I put this in E1

=TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",99)),199))

For the part No
=SUBSTITUTE(A1,E1,"")
This second formula is dependent on the first

Mike
 
Thanks! It worked. But what if I have more than two words in a part
description? i.e., "Ceramic Insulators, Coils and Resistors"
 
Hi,
I'm trying to split a cell that contains a part number and a part
description into a cell that contains the part number and a cell that
contains the part description. There are about 7,000 cells that need to be
separated this way. I tried the text to columns wizard but that is very
labor intensive since some part numbers have spaces and letters in them.
Example:
124 TD45 88 Ceramic Insulators
12 671 6773 Copper Coils
116011 Variable Resistors

I got as far as the formula =MID(A1,1,FIND(" ",A!,1)-1)
but that only returns the first group of letters ("124", "12", "116011") how
can I extract "124 TD45 88", "12 671 6773", and "116011" into their own cells
and "Ceramic Insulators", etc. into their own cells?

Thanks for your help,
Jim
Click to expand...

You need to enunciate a rule that can reliably separate the part number from
the description.

In the examples you give, the description is always preceded by a
<digit><space> and never contains a digit.

If that holds true, you could use this User Defined Function (UDF) to separate
the two parts.

To enter this User Defined Function (UDF), <alt-F11> opens the Visual Basic
Editor.
Ensure your project is highlighted in the Project Explorer window.
Then, from the top menu, select Insert/Module and
paste the code below into the window that opens.

To use this User Defined Function (UDF), enter a formula like

=SplitNumName(A1,TRUE) for the Number portion
=SplitNumName(A1,FALSE) for the description portion

If the pattern is different, we may be able to come up with an appropriate
rule, given enough "off" examples.


=============================================
Option Explicit
Function SplitNumName(s As String, Optional Num As Boolean = True) As String
Dim re As Object
Dim ReturnString As String

Set re = CreateObject("vbscript.regexp")
With re
.IgnoreCase = True
.Global = True
.Pattern = "(.*\d)\s+(\D+$)"
End With

If Num = True Then
ReturnString = "$1"
Else
ReturnString = "$2"
End If

SplitNumName = re.Replace(s, ReturnString)

End Function
=============================
--ron
 
But what if I have more than two words in a part description?

Then it won't work and I don't know how to do it.

Mike
 
Back
Top