splitting up cell contents

Hu · Mar 30th, 2004, 07:30 AM

Hi!

I have following problem, which i would be pleased to have help with. i have a cell containing values separated by ";". i would like to have those seperate values to be indexed somehow (preferaby using a macro) so that i could use appropriate one for the further calculations.

please can anyone give me assistance on this one?

respect
hu

ps i have Excel 2003

Brian from Maui · Mar 30th, 2004, 08:01 AM

Quote:

Originally Posted by Hu

Hi!

I have following problem, which i would be pleased to have help with. i have a cell containing values separated by ";". i would like to have those seperate values to be indexed somehow (preferaby using a macro) so that i could use appropriate one for the further calculations.

please can anyone give me assistance on this one?

respect
hu

ps i have Excel 2003

Have you tried recording a macro using Data/Text to Columns, using the ; as a delimiter?

Hu · Mar 30th, 2004, 08:11 AM

well... no.
because it will not yield the results required.
i need to split the value into respective number of parts (using ";" as delimiter) so that i could call up the needed value according to further calculation concept. let's suppose A1 contains following value "a;b;c;d;e" (except for ""). i would like to be able to reference to each one of the elements for use in Select Case statement. is this clear?

respect
hu

Brian from Maui · Mar 30th, 2004, 08:17 AM

Quote:

Originally Posted by Hu

is this clear?

respect
hu

Yes it is! I'll leave this for the VBA guys!

Hu · Mar 30th, 2004, 08:22 AM

well...

thank you for trying!

respect
Hu

robw · Mar 30th, 2004, 03:20 PM

Hi

This is a UDF I use a lot...It may help you here

'Extract "field" from string
'
'
' field = fsplit(str,fs,jf)

' str = string containg field(s)
' fs= field separator string (normally a single char)
' jf = index of field required
' 0 = return number of fields in string
' +n = return nth field from start of string (ie. use +1 to get first field)
' -n = return nth field from the end of the string (ie. use -1 to get last field)
'
' field = Returned field (or field count)
'
' Notes : 1) returns a null string if you ask for a field that doesn't exist
' 2) if str is non-null but does not contain fs then there is precisely one field in str (even if fs is null)
' 3) if str is null then there are no fields in the string (unless fs =null too, in which case there is precisely one, namely null)
'

'
' #### Future improvements
' A) for fs = " " , treat multiple blanks as a single field separator
' (this is left as an exercise for the reader, cos I cannot be bothered right now)
'
Function fsplit(str, fs, jf)

Dim f As Variant

f = Split(str, fs, -1)
nf = UBound(f) + 1

If jf = 0 Then
fsplit = nf
ElseIf jf > 0 Then
If jf > nf Then
fsplit = ""
Else
fsplit = f(jf - 1)
End If
Else
If jf < -nf Then
fsplit = ""
Else
fsplit = f(nf + jf)
End If
End If

End Function

Any use?

Rob

Mar 30th, 2004, 07:30 AM	#1
Hu Join Date: Apr 2003 Location: Latvia Posts: 56	splitting up cell contents Hi! I have following problem, which i would be pleased to have help with. i have a cell containing values separated by ";". i would like to have those seperate values to be indexed somehow (preferaby using a macro) so that i could use appropriate one for the further calculations. please can anyone give me assistance on this one? respect hu ps i have Excel 2003

Mar 30th, 2004, 08:11 AM	#3
Hu Join Date: Apr 2003 Location: Latvia Posts: 56	Re: splitting up cell contents well... no. because it will not yield the results required. i need to split the value into respective number of parts (using ";" as delimiter) so that i could call up the needed value according to further calculation concept. let's suppose A1 contains following value "a;b;c;d;e" (except for ""). i would like to be able to reference to each one of the elements for use in Select Case statement. is this clear? respect hu

Mar 30th, 2004, 08:22 AM	#5
Hu Join Date: Apr 2003 Location: Latvia Posts: 56	Re: splitting up cell contents well... thank you for trying! respect Hu

Mar 30th, 2004, 03:20 PM	#6
robw Join Date: Dec 2002 Location: ashbourne, derbyshire, uk Posts: 86	Re: splitting up cell contents Hi This is a UDF I use a lot...It may help you here 'Extract "field" from string ' ' ' field = fsplit(str,fs,jf) ' str = string containg field(s) ' fs= field separator string (normally a single char) ' jf = index of field required ' 0 = return number of fields in string ' +n = return nth field from start of string (ie. use +1 to get first field) ' -n = return nth field from the end of the string (ie. use -1 to get last field) ' ' field = Returned field (or field count) ' ' Notes : 1) returns a null string if you ask for a field that doesn't exist ' 2) if str is non-null but does not contain fs then there is precisely one field in str (even if fs is null) ' 3) if str is null then there are no fields in the string (unless fs =null too, in which case there is precisely one, namely null) ' ' ' #### Future improvements ' A) for fs = " " , treat multiple blanks as a single field separator ' (this is left as an exercise for the reader, cos I cannot be bothered right now) ' Function fsplit(str, fs, jf) Dim f As Variant f = Split(str, fs, -1) nf = UBound(f) + 1 If jf = 0 Then fsplit = nf ElseIf jf > 0 Then If jf > nf Then fsplit = "" Else fsplit = f(jf - 1) End If Else If jf < -nf Then fsplit = "" Else fsplit = f(nf + jf) End If End If End Function Any use? Rob

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode