• 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.

Formule converts dots to commas

A

annlee

New Member
Hi,

I have a formule that pretends to remove the 3 last characters of other column, where it includes a dot (.)

For example: ".EE"

When it did "enter", where is (.) changed to a commas (,).

How can we do to the formule not changed the (.) to (,).

Thank you.
 
Hi Khalid NGO,

The formule is equal to this:

LEFT([Client],IF (SEARCH(“.EE”,[Client],1,0) > 0, SEARCH(“.EE”,[Client],1,0), LEN([Client]))

I don't have the file because I am trying to help a colleague.

I don't know if it helps. The column client is formatted to text.

Thank you.
 
Last edited:
Hi annlee,

W/o file, we can take a few guesses:
  1. Confirm that the formula is exactly as you wrote, specially looking at the quotation marks. Also, your formula has 4 arguments given for the SEARCH function, which only allows 3. I would expect this formula to not be accepted by XL.
  2. To confirm, in the formula after you confirm it, it's changing to:
    LEFT([Client],IF (SEARCH(",EE",[Client],1,0) > 0, SEARCH(",EE",[Client],1,0), LEN([Client]))
    Section of interest bolded by me
  3. If answer to #2 is still yes, check the AutoCorrect settings. Did user setup a odd rule to change periods into commas?
  4. Can you type ".EE" into a blank cell by itself w/o having XL change it?
Also, to shorten up your formula, you can write:
=IFERROR(LEFT([Client],SEARCH(".EE",[Client])-1),[Client])
 
Hi Luke,

I will answer to your sugestions. Thank you.

quote="Luke M, post: 141396, member: 662"]Hi annlee,

W/o file, we can take a few guesses:
  1. Confirm that the formula is exactly as you wrote, specially looking at the quotation marks. Also, your formula has 4 arguments given for the SEARCH function, which only allows 3. I would expect this formula to not be accepted by XL. Yes, it was wrote that formule and it is working, except the dot. I pretend that the formule finds ".EE" and remove these three characters. But the formule remove only "EE" because it changed the dot to comma.
  2. To confirm, in the formula after you confirm it, it's changing to:
    LEFT([Client],IF (SEARCH(",EE",[Client],1,0) > 0, SEARCH(",EE",[Client],1,0), LEN([Client]))
    Section of interest bolded by me I had just tried this suggestion. But in this case maintains the comma and it did not change to dot.
  3. If answer to #2 is still yes, check the AutoCorrect settings. Did user setup a odd rule to change periods into commas? This suggestion is going to options of the excel?
  4. Can you type ".EE" into a blank cell by itself w/o having XL change it? Sorry, this suggestion I do not understand. Your suggestion is to write the formule in a blank cell and see if there is any error?
Also, to shorten up your formula, you can write:
=IFERROR(LEFT([Client],SEARCH(".EE",[Client])-1),[Client]

I will try this formule tomorrow. Maybe with this formule it does not change the dot to comma.

Thank you!
 
Good luck annlee. For #3, yes, it is under Excel Options - Proofind.
upload_2015-5-13_8-20-41.png

For #4, I just wanted you to find a blank cell and type the string ".EE" amd confirm (not a formula), just to see if the AutoCorrect was changing it.

I still have my suspicians about the formula itself, as your formula had the 4 arguments in SEARCH, which XL won't accept. But, hopefully my proposed formula fixes the issue.
 
You must log in or register to reply here.
Back
Top