Formula to pull data out of a cell

**SEMMatt** · 09-27-2011, 08:24 PM

Hello,

I have some text data in a cell. I want to pull out some data that's in parentheses, for instance like this:

NEAR -R-TRAIN (BAY RIDGE) $1250 1bd

How can I pull out the words BAY RIDGE and put them in another cell?

Thanks!

**teylyn** · 09-27-2011, 08:29 PM

Hello,

with the text in A1, this formula will extract the text between the round brackets

=MID(A1,FIND("(",A1)+1,FIND(")",A1)-FIND("(",A1)-1)

cheers,

**JBeaucaire** · 09-27-2011, 08:30 PM

Put that string in A1, this formula in B1:

=TRIM(MID(SUBSTITUTE(A1, ")", REPT(" ", 100)), FIND("(", A1) + 1, 99))

**SEMMatt** · 09-27-2011, 08:40 PM

Thanks Teylyn. Jerry, thanks for posting but teylyns worked first so that's what I'm using.

**SEMMatt** · 09-27-2011, 09:17 PM

Hi Guys,

Need to do a similar task. Have this string of money amount,$1200,which I need to locate. It's the only dollar sign in the cell so I think that I can search by that. The amount of space needed after the sign will usually be 4 but may be 3 (as in $900).

Need a formula though.

**teylyn** · 09-27-2011, 09:31 PM

This will return the value as a number

=TRIM(MID(A1,FIND("$",A1)+1,4))+0

if you want the value as text, remove the +0 from the formula.

This will work for numbers with 3 or four digits.

**SEMMatt** · 09-27-2011, 09:43 PM

teylyn, That worked brilliant too! I have a few more. (I'm trying to do these on my own but I'm still a beginner).

I have this text: 1bd and I need to search for it in a cell and print the number 1 in my new cell. Alternatively, it could be 2bd and print 2.

**SEMMatt** · 09-27-2011, 10:36 PM

this is what I've got for that last one:

=MID("bd",FIND("bd",Sheet1!V27,1),-1)

**teylyn** · 09-28-2011, 03:12 AM

SEMMAtt,

you should start a new thread when you are asking a new question. Otherwise, how can you ever mark a thread as "solved", indicating that the question asked in your first post has been answered? Tacking on more and more follow-up questions has a few disadvantages. A new question with 0 replies will attract much more attention from a wider range of members than an old thread with dozens of replies.

So, please, start a new thread for this latest question.

It would also help if you could attach an Excel workbook with a data sample, where you manually enter the desired result you want to achieve.

cheers,

**SEMMatt** · 09-28-2011, 09:27 AM

teylyn,

Sorry about the confusion. I closed this post after a few brilliant answers were received from you and Jerry. I thought that I could take your answers and apply them to my other related questions but turns out I needed more help. I reopened the post because all my questions are similar I think.

I'm closing this post as Solved now and reposting here as you suggest: http://www.excelforum.com/excel-gene...ml#post2608776