Splitting text and numbers from a cell

[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

 

[Mike H]

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

 

[JimAA]

Thanks! It worked. But what if I have more than two words in a part
description? i.e., "Ceramic Insulators, Coils and Resistors"

 

[Ron Rosenfeld]

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

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

 

[Mike H]

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

 

[JimAA]

Beautiful! Worked like a charm. Thank you for your help.

 

[Ron Rosenfeld]

Beautiful! Worked like a charm. Thank you for your help.

Glad to help. Thanks for the feedback.
--ron