PDA

View Full Version : Solved: split line in batchs



CCkfm2000
01-26-2006, 08:18 AM
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
01-26-2006, 08:26 AM
Hi,

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

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

mvidas
01-26-2006, 08:53 AM
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: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 FunctionThat 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
01-26-2006, 09:09 AM
thanks for the reply..

with the user-defined all i get #name?

back to the drawing board for me...

mvidas
01-26-2006, 09:10 AM
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
01-26-2006, 09:19 AM
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
01-26-2006, 09:25 AM
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
01-26-2006, 09:30 AM
thanks alot mvidas you've been a star today.

Zack Barresse
01-26-2006, 01:01 PM
Um, why exactly would one want to use a UDF with RegExp instead of a native formula combination?? Especially one so fast!?!

mvidas
01-26-2006, 01:22 PM
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
01-26-2006, 03:47 PM
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. :yes

mvidas
01-27-2006, 06:42 AM
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! :doh:

CCkfm2000
01-27-2006, 09:09 AM
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
01-27-2006, 09:19 AM
Did you change A1 in Zack's function to C5?

CCkfm2000
01-27-2006, 09:27 AM
yes

Zack Barresse
01-27-2006, 09:28 AM
Hmm, interesting. Can you post a sample spreadsheet with a few cells containing your data set? I'm rather intrigued now..

CCkfm2000
01-27-2006, 09:37 AM
here it is...

mvidas
01-27-2006, 09:56 AM
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
01-27-2006, 10:13 AM
sorry sorry sorry :dunno i should have made myself more understanding in what i needed. :banghead:

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

Zack Barresse
01-27-2006, 10:15 AM
Ah! I see! I was misunderstaning you.

CCkfm2000
01-27-2006, 10:37 AM
thanks anywhere for all the help

CCkfm2000
01-27-2006, 10:55 AM
thanks again.