Separate Parts of a Name String into Different Cells

[icodehelp]

Hi,

I'm new to programming. I'm using Excel 2003. I'm trying to parse out parts of a name string.

The name string is in the following format:
YYMMDD_SN###_###_BB####_Summary.xls
The name string is in Column A.
I want the SN portion to be copied to Column B. The SN number may be variable length.
I want the BB portion to be in Column C. The BBnumber may be variable length.

[Bob Phillips]

=MID(A1,8,FIND("_BB",A1)-8)

and

=MID(SUBSTITUTE(A1,"_Summary.xls",""),FIND("_BB",A1)+1,99)

[icodehelp]

xld,

The SN number may be 3 or 4 charachters long, and the BB number can be 4-7 characters long. Is there a way to write the Mide functions to account for this?

I was thinking of finding "SN" in the string and copying from "SN" to the underscore. (The same for the BB number.) Is this possible?

[Bob Phillips]

It does!

[mdmackillop]

Here's a simple User Defined Formula (UDF), Paste it in a standard module and enter =Splits(A1,1),=Splits(A1,2) etc. to return the relevant parts

Function Splits(Data As Range, Part As Long)
Splits = Split(Data, "_")(Part - 1)
End Function

[Felipe Dasi]

Hi, I have other example for you:


Option Explicit

'In A1 the word
'In B1 the result
Sub SeparString()
Dim Separ$, Result$
Dim i%, C%

Separ = Chr$(32)
i = 1: C = 2
Result = Cells(1, 1)

Do While i <> 0
i = InStr(Result, Separ)
If i = 0 Then Cells(1, C) = Result: Exit Sub
Cells(1, C) = Left(Result, i - 1)
Result = Right(Result, Len(Result) - i)
C = C + 1
Loop

End Sub

[mdmackillop]

Hi Felipe,
When you post code, please select it and click the VBA button to format is as shown,
Regards
MD

[Felipe Dasi]

Ok, tks

[icodehelp]

xld,

When I use your code, I get an error about the Find function:

"Compile error: sub or function not defined"

How do I fix this?

[Bob Phillips]

Use it as a worksheetfunction, not VBA. VBA is overkill for this simple task.