Solved: split line in batchs

[CCkfm2000]

hi all...

i need to split a line into batchs

from 23C10L1P in location c5
from 13C9L1P in location d5
from 17C8L1P in location e5

to

23 in location d5
10 in location e5
1 in location f5

got the first part by using =left(c5,find("c")-1) which gave me 23
the second and last part is a total lost to me.


thanks and regards

cckfm2000

[Zack Barresse]

Hi,

If it will always be that structure, you could just use ..

=MID(A1,6,2)&" "&TRIM(RIGHT(A1,FIND(" ",A1,6)+1))

[mvidas]

cckfm2000,

If you'd be open to using a user-defined function, you could add the following to a standard module of the workbook you need this in:[vba]Function ExtractNumbers(ByVal FullString As String, ByVal WhichNumber As Long) As Long
Dim RegEx As Object, RegC As Object
Set RegEx = CreateObject("vbscript.regexp")
RegEx.Pattern = "\d+"
RegEx.Global = True
If RegEx.Test(FullString) Then
Set RegC = RegEx.Execute(FullString)
If WhichNumber <= RegC.Count Then
ExtractNumbers = RegC(WhichNumber - 1)
Exit Function
End If
End If
Set RegEx = Nothing
Set RegC = Nothing
End Function[/vba]That way, you'd only need to use a formula like:
=EXTRACTNUMBERS(C5,1) to get 23
=EXTRACTNUMBERS(C5,2) to get 10
=EXTRACTNUMBERS(C5,3) to get 1
if the number doesn't exist ( =EXTRACTNUMBERS(C5,4) ) then a zero is returned.

[CCkfm2000]

thanks for the reply..

with the user-defined all i get #name?

back to the drawing board for me...

[mvidas]

Did you put it in a standard module in the workbook you're calling it from? ie if you're calling it from book1.xls, did you put it in a module in book1, or in an installed addin? Either would allow it to work, however if you put it in personal.xls it would not.

[CCkfm2000]

kept getting the error when i was using excel 97 sr-2 but just had a go in excel 2000 and it works

magic... wow thats looks very good..

thanks

[mvidas]

Makes sense, I didnt think about version differences..
xl97 should have been able to use udf's (dont quote me on that), but I don't believe the vbscript/regexp was available in 97. If you need something for 97 we could write that too, though it wouldnt be as sleek as the regexp version

Let us know if you need anything else!

[CCkfm2000]

thanks alot mvidas you've been a star today.

[Zack Barresse]

Um, why exactly would one want to use a UDF with RegExp instead of a native formula combination?? Especially one so fast!?!

[mvidas]

Zack,

Native functions would have been better, I was just under the assumption the letters in the cells may change. If There will always be a C,L,P in them then these formulas would work:

1st number =LEFT(C5,FIND("C",C5)-1)
2nd number =MID(C5,FIND("C",C5)+1,FIND("L",C5)-FIND("C",C5)-1)
3rd number =MID(C5,FIND("L",C5)+1,FIND("P",C5)-FIND("L",C5)-1)

I thought the regexp version would be more versatile for different situations though. But if the cells will always have the same format, a UDF wouldn't be necessary.

I couldn't get yours to work, I may have misinterpreted A1 though..
Matt

[Zack Barresse]

Copy this into A1:A3...

from 23C10L1P in location c5
from 13C9L1P in location d5
from 17C8L1P in location e5

Then put this in B1...

=MID(A1,6,2)&" "&TRIM(RIGHT(A1,FIND(" ",A1,6)+1))

.. and copy down.

Not dependent on specific characters, but it is dependent on the structure.

And I see why you chose a UDF, makes sense now.

[mvidas]

Ha! Now I definately feel the "you've been a star today" is wrong I read

from 23C10L1P in location c5
from 13C9L1P in location d5
from 17C8L1P in location e5

to

23 in location d5
10 in location e5
1 in location f5

and interpreted it as

23C10L1P is in cell c5
13C9L1P is in cell d5
17C8L1P is in cell e5

to

23 needs to go to cell d5
10 needs to go to cell e5
1 needs to go to cell f5

D'oh!

[CCkfm2000]

using firefytr function
=MID(A1,6,2)&" "&TRIM(RIGHT(A1,FIND(" ",A1,6)+1))
i get #value

but using mvidas function

1st number =LEFT(C5,FIND("C",C5)-1)
2nd number =MID(C5,FIND("C",C5)+1,FIND("L",C5)-FIND("C",C5)-1)
3rd number =MID(C5,FIND("L",C5)+1,FIND("P",C5)-FIND("L",C5)-1)

i get what i need

[mvidas]

Did you change A1 in Zack's function to C5?

[CCkfm2000]

yes

[Zack Barresse]

Hmm, interesting. Can you post a sample spreadsheet with a few cells containing your data set? I'm rather intrigued now..

[CCkfm2000]

here it is...

[mvidas]

CCkfm,

His function was designed to work on cells that have strings like "from 23C10L1P in location c5" (no quotes), as you described in your first post, and outputs strings like "23 in location c5".

Matt

[CCkfm2000]

sorry sorry sorry i should have made myself more understanding in what i needed.

i still got what i wanted thanks to all for all the help

[Zack Barresse]

Ah! I see! I was misunderstaning you.