Complex MID +Find

[rdev]

Hi I have tried hard butt got stuck finally
I have the following string in Cell A1 ,
ZAR 1676979125 I FFVV 24FEB11 | 4715 2747 | 0.50 24− 4− | 28− CCCA513101XXXXXX6557
I want to break up so that I have in cell B1, C1, D1 etc..
ZAR
1676979125
I
FFVV
24FEB11
4715 ( this can vary in terms of character, it is a currency amount)
2747( this too can vary , it is a currency amount
0.5
24
4
28
Can someone help me , i am a trouble getting this right

 

[rdev]

i need to extract the CC XXXX to in an adjacent cell

 

[Haseeb Avarakkan]

Try,

B1, copy across & down...

=TRIM(MID(SUBSTITUTE(" "&TRIM(SUBSTITUTE(SUBSTITUTE($A1,"−",""),"|",""))," ",REPT(" ",250)),COLUMNS($B1:B1)*250,250))

 

[rdev]

Thanks Haseeb
Amazingly easy for you ,for me not , can you explain to me the formula you used

 

[Haseeb Avarakkan]

" "&TRIM(SUBSTITUTE(SUBSTITUTE($A1,"−",""),"|",""))

This part will remove all the '−', '|' & extra spaces from the text, and add a space at the beginning. so you will get

" ZAR 1676979125 I FFVV 24FEB11 4715 2747 0.50 24 4 28 CCCA513101XXXXXX6557"

SUBSTITUTE(" "&TRIM(SUBSTITUTE(SUBSTITUTE($A1,"−",""),"|",""))," ",REPT(" ",250))

This will change all the space to 250 extra spaces.

MID(SUBSTITUTE(" "&TRIM(SUBSTITUTE(SUBSTITUTE($A1,"−",""),"|",""))," ",REPT(" ",250)),COLUMNS($B1:B1)*250,250)

COLUMNS($B1:B1) will give you 1, so 1*250 = 250

On B1 MID function start_num will set as 250, num_chars 250.

After each item you have 250 extra spaces. " ZAR " (250 space ZAR 250 space) so on for all items.

TRIM(MID.....) will remove the extra spaces.

When copy to C1, COLUMNS($B1:C1)*250 will give, 2*250 = 500,

MID start_num 500, num_chars 250. TRIM will remove the extra spaces. so on....

I am sorry if it is not clear

 

[Peter_SSs]

You could also get very close with Data|Text to Columns|Delimited using space and "|" as the delimiters. It would leave the trailing "-" after those numbers which may be a problem, though you could use Find/Replace afterwards to remove those.