extracting partial cell value 1

[gestaltphoenix]

Hey all...

I know this is probably basic but I'm a little pressed for time to look it up at the moment. I have a cell value in the format as follows:

(194) 3151-05B PAR TELSET MLS 12B BLK 7311H05A

How can I write a formula that will extract just the number in parantheses? The numbers change in digit length, but will always be enclosed as shown. Any thoughts?

 

[xlbo]

=MID(A1,2,FIND(" ",A1,2)-3)
will do the trick but please bear in mind that this site is not here to act as a helpdesk for you

Rgds, Geoff
Si hoc legere scis, nimis eruditionis habes
Want the best answers to your questions ? - then read me baby one more time - faq222-2244

 

[combo]

This formula should return the number:
=VALUE(MID(A1,FIND("(",A1)+1,FIND(&quot",A1)-FIND("(",A1)-1))

combo

 

[xlbo]

ooops:
=MID(A1,2,FIND(&quot",A1,2)-3)


Rgds, Geoff
Si hoc legere scis, nimis eruditionis habes
Want the best answers to your questions ? - then read me baby one more time - faq222-2244

 

[BenUK]

The below function will work:

strInput equals your cell value

Public Function ExtractValue(strInput As String) As Long

If strInput > "" Then
i = 0
Do While Not x = Chr$(41)
i = i + 1
x = Mid(strInput, i, 1)
Loop

ExtractValue = Mid(strInput, 2, i - 2)
End If

End Function

 

[BenUK]

That one was just for you Geoff

 

[gestaltphoenix]

Thanx for the help combo, et al...

And xlbo, as described at the bottom of this very page,

"DESCRIPTION: Microsoft: Office technical support forum and mutual help system for computer professionals"

I had a task, I needed help. If you dont like the question, don't answer. Thanx all the same though.

Regards

GP

 

[xlbo]

gestaltpheonix - I didn't mean to cause offence but the line:
"I'm a little pressed for time to look it up at the moment"
kinda inplies laziness - plus it would've taken less time to look up the FIND function than to post here. You are correct in that if I don't like the question, I wouldn't have answered but please don't include any phrases like that again. BTW:

"mutual help system "
Member profile:
gestaltphoenix (IS/IT--Manageme)

Replies To Other Members' Threads: 0

This is NOT a personal helpdesk - it is a forum for people to SHARE information NOT for some to give whilst others only receive. Of course, we don't expect everyone to be able to help all the time and of course some know more than others but it IS expected that if you receive help, you learn from it and you use the knowledge gained to help others (here and elsewhere). This is not my opinion, it is site policy

Rgds, Geoff
Si hoc legere scis, nimis eruditionis habes
Want the best answers to your questions ? - then read me baby one more time - faq222-2244

 

[gestaltphoenix]

xlbo

No offence taken...

As it stands, my working knowledge of Excel is limited to the point where I wouldnt know where to start looking, leading me to put myself at the mercy of the forum experts. Whilst many beginners could find the answers on their own by looking things up, it is considerably quicker to ask someone. Everyone nowadays is pressed for time, so my expression (if inappropriate) was one born of our fast-paced culture. If not my expertise, I can at least offer my thanks for taking time out of your day for the responses.

You'll also notice that I've only posted 7 times in the past year, a figure not exactly a drain on the community. However, posting a question, especially if its a novice one still benefits the forum, albeit in a more passive manner. I searched previous posts to some degree with no satisfaction (as to not trouble anyone unnecessarily). Repeated questions, while perhaps a nuisance to forum veterans, are nice to have from a novice perspective so as not to have to dig too deep through the archives. As I build my knowledge base of the subject matter, in time I too will offer up help, should my expertise fit a question.

Again, my thanks for your time and help...

Regards

GP

 

[xlbo]

GP - no problem - I completely agree with you and appreciate the fact that you used the archives 1st. I would just suggest not using a similar line as the one you did or also stating that you have already searched the archives.
Have a good weekend and I'm glad your problem got solved

Rgds, Geoff
Si hoc legere scis, nimis eruditionis habes
Want the best answers to your questions ? - then read me baby one more time - faq222-2244