Formula only working after I delete and add space between words.

[Eamonn100]

Does any one know what's wrong in this instant?

When I copy and paste "3 Months" to my spreadsheet, the formula in the cell next to it comes up #N/A. Only after deleting and adding the space between 3 and Months does the formula work. How can I make it read 3 Months first time ever time?


What I'm getting,

3 Months

#N/A

<tbody>
</tbody>

The formula.

=(INDEX($E:$E,MATCH("3 Months",$E:$E,0)))


What I want to end up with, without having to delete and add the space.

3 Months

3 Months

<tbody>
</tbody>

 

[Declamatory]

That seems strange. I pasted 3 months into cell E1 and your formula into Cell F1. The result I get is 3 months not #N/A

 

[Mackers]

Hi

You might have a character that looks like a space but isn't a space there, for example a non breaking space. With your "3 months" in cell A2, use the formula =CODE(MID(A2,2,1)) and see what it returns. A normal space should be the number 32. Anything else means you have something else in there.

 

[Eamonn100]

That seems strange. I pasted 3 months into cell E1 and your formula into Cell F1. The result I get is 3 months not #N/A

Yes when I type in 3 Months it works for me but when I copy it for the web page, it won't read it without me having to delete and add the space again.

 

[Eamonn100]

@ Mackers.............I got 117.

 

[Mackers]

CHAR(117) on my excel is a lower case 'u' but it might differ between excel versions or something.

You can try cleaning your input by using the formula =SUBSTITUTE(A2,CHAR(117)," ") where your pasted data is in A2 and 117 is the code you're getting for the space in the cell. Hopefully what this will do is replace any instance of the unusual character with a space. You can then use your original formula to search this newly cleaned data.