String Extract options

Hi,

 

Here are various method to work on some data within the string

 

Data

A5 = Custom Audience Blend 110.00 132,439.34

 

 

1)      Need “ data after first Space”

A6                         =             =TRIM(MID(A5,FIND(" ",A5),100))

Output                 =             Audience Blend 110.00 132,439.34

 

2)      Put a Special character for the last set of data easy to split

 

A6                         =             SUBSTITUTE(A5," ","^",(LEN(A5)-(LEN(SUBSTITUTE(A5," ","")))))

Output                 =             Custom Audience Blend 110.00^132,439.34

 

Varaible               Len(a5)                                               =             39

                              (LEN(SUBSTITUTE(A5," ","")))                      =             35          

                                             (SUBSTITUTE(A5," ",""))                 =               CustomAudienceBlend110.00132,439.34

 

 

 

               Repeat the same process on the resultant data to extract number of last columns