How can I trim "4|+|23.68^" into "23.68" in excel 2000?

  • Thread starter Thread starter Guest
  • Start date Start date
If the number that you want to extract always begins after second instance
of "|" and ends just before "^" then use the following: -
=--(MID(A1,FIND("xx",SUBSTITUTE(A1,"|","xx",2))+1,FIND("^",A1)-(FIND("xx",SUBSTITUTE(A1,"|","xx",2))+1)))

If the number that you want to extract always begins after second instance
of "|" and ends just before "^" then use the following: -
 
Thank you very much! The formula solved my problem.

Best regards,

George
 
N Harkawat said:
If the number that you want to extract always begins after second instance
of "|" and ends just before "^" then use the following: -
Click to expand...
....

If these assumptions hold, better to use Edit > Replace to eliminate the ^s,
then use Data > Text to Columns, Delimited, specify | as the delimiter, and
skip the first two fields.
 
You must log in or register to reply here.

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

TRIM IF CELL CONTAINS ONE WORD, ABBREVIATE IF IT CONTAINS MULTIPLE WORDS 1
Scrabble Value calculation for Welsh words 0
How to use trim finction in array 3
Trimming letters in an Excel field 2
TRIM Q 3
Excel Import XML into Excel 0
TRIM not trimming 5
Alt codes 4

Back
Top