Split specific data into different cells

[rfreeman]

Hello there,
I need some help with trying to split specific data from cell into 2 different cells. For example I some summary information in 1 cell which needs to split into 2 different cells for deeper reporting purposes.

The data example is a follows:

A1	Current State
A2	- Date in market: 29-12-21 - Technical complexity: 2
A3	- Date in market: 29-12-21 - Technical complexity: 2

I need to split the data in cell A2, so that the only the date is extracted to a new cell B3. Then I need the 2 from Technical Complexity to be extracted to a new cell C3.

Attached is an excel example of how I need the finished data to appear, however I am having issue using the right formula to split the data as needed

If anyone can help me work this out I would be very grateful

Regards
Rohan

 

[pecoflyer]

To extract the date :=TRIM(MID(A2,FIND(":",A2)+1,9))+0 and format as needed
For the number =trim(right(a2,1))+0

 

[bosco_yip]

In D2, formula copied across right to G2, and all copied down :

=0+MID(LEFT($A2&" "&$B2,IF(E$1="",199,SEARCH(E$1,$A2&" "&$B2)-4)),SEARCH(D$1,$A2&" "&$B2)+LEN(D$1)+2,99)

Then,

• Select D2:D5 >> Format Cell >> "Date" >> choose: "d/m/yyy"
• Select E2:F5 >> Format Cell >> "General"
• Select G2:G5 >> Format Cell >> Currency >>" Decimal place" : 0 and "Symbol" : $

 

[rfreeman]

Thanks for the responses. I was trying to use a combination of LEFT & FIND formulas but was getting stuck

 

[rfreeman]

Thanks @bosco_yip for your very detailed response. The formula you provided is very complex and I am trying hard to understand all the sub-strings.
It does work, however for my own learning I am trying to understand this a bit better so I can follow all the steps

 

[bosco_yip]

Thanks @bosco_yip for your very detailed response. The formula you provided is very complex and I am trying hard to understand all the sub-strings.
It does work, however for my own learning I am trying to understand this a bit better so I can follow all the steps

If you find the above formula (used 6 functions) in some complex, you could try another a bit shorter formula (used 3 functions) as in :

=FILTERXML("<a><b>"&SUBSTITUTE(SUBSTITUTE($A2&$B2,D$1&": ","<r/>"),"- ","</b><b>")&"</b></a>","//b[r]")

 

[rfreeman]

Hi again team, thanks so much for the help with the formulas. I ended using a TRIM function to get the desired result as I found the string the easiest to follow and explain.

The end result data summary is now working well, however I have a side issue that is related to additional incomplete data.
The Current State & Required Capabilities fields are always pre-populated with in cell titles, but the actual data is not populated until after further review. So the issue I have is when there is no date, or number or $ figure, I get an #VALUE error in my original formula.

Can anyone help me try work out how to return a blank value if one of the pre-defined conditions is not met?

I have attached a further live example sheet for further understanding, as you will se in rows 9-11

Thanks again!

 

[pecoflyer]

You can wrap the formula with an IFERROR function like =IFERROR(IF($H9="";"";TRIM(MID($H9;FIND(":";$H9)+1;9))+0);"")

 

[rfreeman]

Yep that is perfect, I cant believe I didn't think of that already. Thanks so much!