Thread: Separate a string Thanks: 0 Likes: 0

1. Separate a string

Hello,

I need help separating a string in cell AI. The string is:

X0000357_1212011_USD_1359376.33 which is concatenated DealID "X0000357, Date "12/1/2011" Currency "USD" and Amount "1359376.33. As you can see they are separated by underscore _. In B1 I need a formula to extract the Deal ID X0000357; in C1 I need a formula to extract the Date 1212011 and format it 01-dec-2011; in D1 I need a formula to extract currency USD and E1 a formula to extract the amount 1359376.33.

3. Re: Separate a string

Thanks, that works. But what would be the formulas as I described? It will be much helpful to have a formula> Thanks.

4. Re: Separate a string

Try these...

Code:
```B1:  =LEFT(A1,FIND("_",A1&"_")-1)

C1:  =--TEXT(TRIM(MID(SUBSTITUTE(A1,"_",REPT(" ",LEN(A1))),LEN(A1)*(1),LEN(A1))),"0-00-0000")

D1:  =TRIM(MID(SUBSTITUTE(A1,"_",REPT(" ",LEN(A1))),LEN(A1)*(2),LEN(A1)))

E1:  =TRIM(MID(SUBSTITUTE(A1,"_",REPT(" ",LEN(A1))),LEN(A1)*(2),LEN(A1)))```
You will need to format the cells in Column C using this Custom Format pattern.... dd-mmm-yyyy

5. Re: Separate a string

Hi your E1 formula is identical to D1 and therefore returns the same value as D1. I need the formula to return the amount.

6. Re: Separate a string

Hi there any comments to my last question?

7. Re: Separate a string

Notice a trending change in the functions? Try changing E1 to multiply by 3 instead of 2.

Still, why not use Text to Columns? It's vastly more efficient, and you won't have to worry about syntax that might not be transparent to you...

8. Re: Separate a string

You might end up with a problem in C1, too. Try this:

=DATEVALUE(TEXT(TRIM(MID(SUBSTITUTE(A1,"_",REPT(" ",LEN(A1))),LEN(A1)*1,LEN(A1))),"0-0-0000"))

9. Re: Separate a string

Perfect!. Thank you

10. Re: Separate a string

Originally Posted by NateO
You might end up with a problem in C1, too. Try this:

=DATEVALUE(TEXT(TRIM(MID(SUBSTITUTE(A1,"_",REPT(" ",LEN(A1))),LEN(A1)*1,LEN(A1))),"0-0-0000"))
I missed the December 1st date, but I don't think your formula is the answer as it will fail for a date like 11222011 (that is 11/22/2011). The problem is the OP's decision to recognize 1212011 as December 1, 20011 instead of January 21, 2011. The only way I can see that being a valid decision is if the dates are always the first of the month, but we have not been told that is the case.

User Tag List

Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•