Solved I am having a problem with extracting values from one cell

July 27, 2012 at 02:38:28
Specs: Windows 7
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

See More: I am having a problem with extracting values from one cell

Report •


#1
July 27, 2012 at 06:00:50
✔ Best Answer
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.


Report •

#2
July 30, 2012 at 00:38:19
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

Report •

#3
July 30, 2012 at 02:50:34
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.


Report •

Related Solutions


Ask Question