Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 6
Default String manipulation!!

Hi,

In my Excel sheet I have a cell with the following string:

Field\SM-A1\SEM A P03\Hydraulic sensors\PT01-LP Supply

What I need to do is extract everything after the 2nd backslash and before
the 3rd.
Leaving:

SEM A P03

I can't just count as the value could change from either end i.e SM-A1 could
become SM-A100. So as the string will change, I need to test the string, and
I can't think how....

Any help would be great!
Thanks in advance

Manny
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 2,253
Default String manipulation!!


you could use the very handy split function

if you have 10000 function calls it's maybe not the fastest solution,
but it's ultra easy.

Sub Parser()
'Note works only for xl2000+
Dim path$, aTmp, sRes$
path = "Field\SM-A1\SEM A P03\Hydraulic sensors\PT01-LP Supply"

'note split will give a 0based array
sRes = Split(path, "\")(2)
MsgBox sRes

End Sub



keepITcool

< email : keepitcool chello nl (with @ and .)
< homepage: http://members.chello.nl/keepitcool


"?B?TWFubnlsdWs=?=" wrote:

Hi,

In my Excel sheet I have a cell with the following string:

Field\SM-A1\SEM A P03\Hydraulic sensors\PT01-LP Supply

What I need to do is extract everything after the 2nd backslash and
before the 3rd.
Leaving:

SEM A P03

I can't just count as the value could change from either end i.e SM-A1
could become SM-A100. So as the string will change, I need to test the
string, and I can't think how....

Any help would be great!
Thanks in advance

Manny


  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 5,651
Default String manipulation!!

On Mon, 18 Oct 2004 03:07:02 -0700, "Mannyluk"
wrote:

Hi,

In my Excel sheet I have a cell with the following string:

Field\SM-A1\SEM A P03\Hydraulic sensors\PT01-LP Supply

What I need to do is extract everything after the 2nd backslash and before
the 3rd.
Leaving:

SEM A P03

I can't just count as the value could change from either end i.e SM-A1 could
become SM-A100. So as the string will change, I need to test the string, and
I can't think how....

Any help would be great!
Thanks in advance

Manny


As a worksheet function:

=MID(A1,FIND("~",SUBSTITUTE(A1,"\","~",2))+1,
FIND("~",SUBSTITUTE(A1,"\","~",3))-
FIND("~",SUBSTITUTE(A1,"\","~",2))-1)

As a VBA function in Excel 2000 and later:

================
Function foo(str As String)
Dim temp

temp = Split(str, "\")
foo = temp(2)

End Function
==================


--ron
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
String Manipulation String Manipulation Excel Discussion (Misc queries) 3 December 1st 05 12:51 AM
string manipulation banavas[_16_] Excel Programming 2 July 9th 04 07:55 AM
VBA String manipulation Frank Kabel Excel Programming 2 March 5th 04 08:19 AM
VBA String manipulation Chip Pearson Excel Programming 0 March 5th 04 12:02 AM
String Manipulation Ray Batig Excel Programming 3 December 23rd 03 01:31 AM


All times are GMT +1. The time now is 07:22 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"