Using a function to break apart a variable length field 2

[SpaghettiStraps]

howdy,

i have a field in an excel spreadsheet that has something like 1234-198 or 123-1988. it can have 4 or 3 digits before the dash and up to 10 characters after the dash. i want to break this out into two separate fields using the dash as the breakpoint. (i don't want the dash in either field.)

ordinarily i would use MID() but because of the variable length issue i don't know how.

if you can help you'd save me a heck of a lot of work every month.

thanks,
john

 

[RoyVidar]

Hi!

I'm not sure how to do this within the spreadsheat, but providing you with some VBA code that should do the trick. Note, should work in Excel 2000+ versions

The InStr function gives the position of a string within the string.

[tt]Public Function GetLeftChars(byval sText as string) as String
GetLeftChars = mid$(sText, 1, InStr(sText, "-&quot-1)
end function

Public Function GetRightChars(byval sText as string) as String
GetRightChars = mid$(sText, InStr(sText, "-&quot+1)
end function[/tt]

These two snippets might be pasted into a module in VBA, and be referenced as ordinary functions in Excel:

[tt]=GetRightChars(A1)[/tt]

(If you don't know how enter the VBA coding window:
Hit ALT+F11. In the Insert menu, select Module - and there paste the functions)

HTH Roy-Vidar

 

[sfvb]

If the value you were trying to parse was in cell A1, and you wanted the 1st half in cell B1 and the last half in cell C1, then this will work:

Formula for cell B1

=IF(ISERR(FIND("-",A1,1)),A1,LEFT(A1,(FIND("-",A1,1)-1)))

Formula for cell C1

=IF(ISERR(FIND("-",A1,1)),"",RIGHT(A1,LEN(A1)-FIND("-",A1,1)))

If the cell doesn't have a hyphen in it, then the whole value of A1 will be in cell B1.


If you're absolutely positive that the cells will always have a hyphen in them then this is a shorter forumla:

Formula for cell B1

=LEFT(A1,(FIND("-",A1,1)-1))

Formula for cell C1

=RIGHT(A1,LEN(A1)-FIND("-",A1,1))

Steve

 

[SpaghettiStraps]

thanks guys. i had already tried hth roy-vidar's visual basic method and it worked perfectly. i didn't know using vb was so easy. hehe

i will try your method too steve so that i can understand the excel method.

you guys are the best.

 

[sfvb]

Since you are in effect splitting, the cell. I'm throwing in the underused Split function. I'm just going to modify RoyVidar's existing function.

The Split function creates a zero based array that is delimited by whatever you choose. In this case a hyphen. (If no delimiter is used, then the Split function uses the space character as the delimiter.)

Public Function GetLeftChars(ByVal sText As String) As String
  GetLeftChars = Split(sText, "-")(0)
End Function

Public Function GetRightChars(ByVal sText As String) As String
  GetRightChars = Split(sText, "-")(1)
End Function

Steve