need to extract text from a string in excel

[projector1]

i am trying to use the text function to copy a certain part of the string

regsvr32.exe /s /c expsrv.dll

regsvr32.exe /s /c INETWH32.dll

regsvr32.exe /s /c MSJINT35.DLL

i need to copy the text after the space that appears after "/c"

the output i am looking for is

 

expsrv.dll

INETWH32.dll

MSJINT35.DLL

 

the only thing is i only know how to use the LEFT or RIGHT function from

and i know there is a way where you can start from a particular point

 

thanks

[riedquat]

=RIGHT(A1,LEN(A1)-SEARCH("/c",A1)-2)

 

assuming that your text you want to extract FROM is in column A

 

or

 

=RIGHT(A1,LEN(A1)-19)

 

assuming the text to the left of the file name is the same number of characters.

[acb_]

the only thing is i only know how to use the LEFT or RIGHT function from and i know there is a way where you can start from a particular point

 

That'll be the MID function:

 

MID(CellReference,start point in string,number of characters to return)

[projector1]

hats off to you!

thanks

 

my next question is i have a bat file that copies some ocx and dll files to the system32 folder. You have taught me how to extract text.

What i need to do now is

replace the "GPRY_CTL.ocx "

 

echo No | copy /-Y L:\Software\GPRY_CTL.ocx C:\windows\systems32

(this line has been repeated in several rows in the excel sheet)

 

with these

 

OLEPRO32.DLL

RDOCURS.DLL

roboex32.dll

scrrun.dll

STDOLE2.TLB

sysinfo.ocx

TABCTL32.OCX

threed32.ocx

Vb5db.dll

VB6STKIT.DLL

vbajet32.dll

 

if you could show me how to do one i am sure i could carry out the rest

 

thanks

[riedquat]

again, assuming your filenames are in column A:

 

=CONCATENATE("echo No | copy /-Y L:\Software\",A1," C:\windows\systems32")

[projector1]

sorry i am gob smacked! very much appreciated!

it has worked and i am on my way!

thank you very much

[riedquat]

no problem

 

and for completeness here it is using MID

 

=MID(A1,20,12) or =MID(A1,20,LEN(A1)-19)

 

and to do it all in one go

 

=CONCATENATE("echo No | copy /-Y L:\Software\",MID(A1,20,12)," C:\windows\systems32")

[mac_shinobi]

Can't you use the split function and store it into an array and then to re call it you would just use the variable name and the index value , here is a vbscript example, but it will be pretty much the same in vba and vb 6 :

 

dim txt,a

txt="Hello World!"

a=Split(txt," ")

msgbox a(0) '<-- will have the word hello

msgbox a(1) '<-- will have the word World!

 

however for you txt will be

 

regsvr32.exe /s /c expsrv.dll

 

and each time you make txt equal to the command ie ( regsvr32.exe /s /c expsrv.dll )

 

you will have to output the last value you want by making a(3)

 

ie telling you want to output value of Index 3 which will be the 4th value.

 

hence outputting the 4th value.