PDA

View Full Version : Separate Parts of a Name String into Different Cells



icodehelp
04-21-2008, 02:30 PM
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
04-21-2008, 02:36 PM
=MID(A1,8,FIND("_BB",A1)-8)

and

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

icodehelp
04-21-2008, 03:43 PM
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
04-21-2008, 04:01 PM
It does!

mdmackillop
04-21-2008, 04:13 PM
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
04-22-2008, 04:54 AM
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
04-22-2008, 05:37 AM
Hi Felipe,
When you post code, please select it and click the VBA button to format is as shown,
Regards
MD

Felipe Dasi
04-22-2008, 05:40 AM
Ok, tks

icodehelp
04-22-2008, 11:22 AM
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
04-22-2008, 12:13 PM
Use it as a worksheetfunction, not VBA. VBA is overkill for this simple task.