I have an excel cell with the following values [I=406;A=5546.55;D=20110520|I=541;A=8486;D=20110520|I=477;A=11.05;D=20110524] and want to put the A and D values in their on cell. ie I want say value of A = 5546.55 should appear in a Cell as 5546.55

Here's a 2 step process: With your long string in A1, use the Data...Text To Columns feature to split the string into separate cells. If you use a semicolon ; and the bar | as your delimiters, you should get something like this:

A B C etc. 1 [I=406 A=5546.55 D=20110520 etc.Once you have that, you can use this formula to extract the numbers after the equal sign:

=SUBSTITUTE(MID(A1,FIND("=",A1)+1,LEN(A1)),"]","")

Obviously, you would need to drag this formula across to update it for each column.

The MID function extracts everything after the equal sign and the SUBSTITUTE function eliminates the bracket at the end of the string if it exists.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Thank you very much and it worked for me. I now want to convert a text like 20120825 to a date format like this 2012/08/25

Since this question is different than your original, please post it in its own thread.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Ask Your Question

Weekly Poll