• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Formula needed for removal of [] and data in.

chetansg

New Member
Can someone help me in providing formula for removing [] and data in it?

DATARESULTS
ABC [2.5], JKB [23.3]ABC, JKB
Norway [.45]Norway
Smaller then1-5 days [12.5],5-12 days [34.5],13-25 days [23.3], 26-50 days [45.2]Smaller then1-5 days, 5-12 days ,13-25 days , 26-50 days
DGF&co (fdg) [3.4]DGF&co (fdg)
GEMINI PLC/THE [5.22], Leo CORP [23..0]GEMINI PLC/THE, Leo CORP
emptyempty
 
Formula solution would probably be too much unless VBA is used.

Other simpler alternative would be:
1. Copy DATA column to RESULTS column
2. Select RESULTS column
3. Invoke "Find and Replace"
4. In Find field, put "[*]" without quotes.
5. Keep replace field blank.
6. Choose Replace all...

59981
 
= IFERROR(TRIM(REPLACE(A1, FIND(" [", A1), FIND("]", A1) - FIND(" [", A1) + 1, "")), A1)
Note: A1 is the cell containing the data i.e., ABC [2.5], JKB [23.3]

This formula works for the single [ ]. It doesn't work for multiple [ ].
 
Back
Top