Retrieving portion of cell value

Hi,

I'm cleaning up some data. I have some fields like this:

LSKFJ(38) (513 Rating)
SDFSDFSDFSD(2352) (71 Rating)
FS(37) (53 Rating)

I want to remove the rating portion from this column and place it into another column. So the first column would look like this:

LSKFJ(38)
SDFSDFSDFSD(2352)
FS(37)

And the second column looks like this:

(513 Rating)
(71 Rating)
(53 Rating)

I've played around with the RIGHT() and FIND() functions, but I haven't managed to achieve what I'm looking for.

Any help on this is greatly appreciated.

Assume the original text is in A1 and then exploit the ") (" occurence. Do you have a ") (" in each cell?

This gets the left hand part:
=LEFT(A1,FIND(") (",A1,1))

And this gets the right hand part:
=RIGHT(A1,LEN(A1)-1-FIND(") (",A1,1))

---EDIT---
Forgot the minus one there in my original post...

Hi hk106,

If all your ratings are two digit numbers you can grab them using
=Right(A1,11)
Then the left side becomes
=Left(A1,Len(A1)-11)

dude your going to include the space with that Left function (hence the minus one in my post) or just subtract 12 in the Left() function case

can you upload an example workbook? Do you still get the error when you replace find (") (",H2,1) with a number?

i was thinking there may be something "strange" about the space character - non-breaking space issue?

I'm not sure what's up, but thought an example book would help out.

Can you find and replace all the ") (" with ")(" (no space)?

Assuming text in cell C5 perhaps you could split it using

=MID(C5,1,FIND(" ",C5)) for the first part and

=TRIM(MID(C5,FIND(" ",C5),LEN(C5))) for rating part.


Alf

@GeneralDisarray,
I've seen many of these data conversions where the assumed space character is really character 160. You really need hk106 to attach an example file to see if the space is really a space. If it isn't a space then =Trim(Left(A1,Len(A1)-11)) won't work either.

Try the attached for Rating numbers of up-to 4 digits in length.

Could "text to columns" work here using a space as the delimiter?

Try this,

C2,

="("&TRIM(RIGHT(SUBSTITUTE(A2,"(",REPT(" ",LEN(A2))),LEN(A2)))

B2,

=SUBSTITUTE(A2,C2,"")

Copy down.

@marvinP, thank you for the info, i couldn't remember what to look up on that issue.

@hk106, Try this...: =RIGHT(A1,LEN(A1)-FIND("(",A1,2)+1)

By setting the start value on the find function to 2, you will skip the first character (which is the first open parenthesis) and find the 2nd occurence.

IF for some reason some of your cells don't start with that character you could use the more general function below to find the second "(".
=RIGHT(A1,LEN(A1)-FIND("(",A1,1+FIND("(",A1,1))+1)

THis should work as long as the data column has the general structure, (something #) (Rating #), where the rating is inside the second set of parenthesis. Anyway, seems to work just find on my test cases.