Searching problems with "~" (tilde) in cells

**HighlandScott** · 01-22-2009, 12:23 PM

Why won't Excel recognise any entry in a cell that contains a ~. For example I have lists of job numbers such as FI9CMR~002 or RE1RE~~004.

The ~ is essential to us in order to pad out the number to 10 characters. This data is exported into excel from another database.

Advice or guidance appreciated.

Thanks

**DonkeyOte** · 01-22-2009, 12:29 PM

[deleted - duplicated below... ?]

**DonkeyOte** · 01-22-2009, 12:30 PM

You mean along the lines of:

=COUNTIF(A1:A10,"*~~*")

Tilde is used to quantify certain characters as literal values... ie often * is used as a wildcard but if you had strings like:

App*le
Apple
Ban*ana
Banana

and you wanted to count only those with * in them the following wouldn't work:

=COUNTIF(A1:A10,"***")
would return 4

You would need to precede the actual * with a ~ so XL knows you're actually looking for that character

=COUNTIF(A1:A10,"*~**")
would return 2

The same is true of tilde itself... ie precede with another ~ so XL knows you're looking for that actual character.

Hope that helps.

**martindwilson** · 01-22-2009, 12:43 PM

if they are causing problems replace the lot with - , find~~ replace -

**HighlandScott** · 01-22-2009, 01:04 PM

I understand what you're saying, but I'm not trying to count, just merely search for a string with a ~ in it.

**HighlandScott** · 01-22-2009, 01:05 PM

Originally Posted by martindwilson

if they are causing problems replace the lot with - , find~~ replace -

Find/replace doesn't work in excel if the string has a ~.

To achieve this I have to take the entire column and paste into notepad(that does recognise the ~) and replace with something else.

**martindwilson** · 01-22-2009, 01:07 PM

want to bet? thats why you use two ~~ to find not one ~ the first one tells it to look for the other

**HighlandScott** · 01-22-2009, 01:08 PM

For what it's worth - the amount of ~ in a string can vary between 1 and 3. For example

FI9C~~~001
FI7RF~~495
FI3FMR~839

Tilde's will only ever be in characters 5-7 of the 10 character string.

**DonkeyOte** · 01-22-2009, 01:09 PM

Originally Posted by HighlandScott

I understand what you're saying, but I'm not trying to count, just merely search for a string with a ~ in it.

Could you post an example of what you're trying to achieve... I'm pretty sure you can do what you want without altering your data.... though I may be wrong... (it's not exactly unheard of!)

**HighlandScott** · 01-22-2009, 01:12 PM

Originally Posted by martindwilson

want to bet? thats why you use two ~~ to find not one ~ the first one tells it to look for the other

No bet! Thanks I can see that does work, so....I'm now able to find/replace tildes without having notepad involved - that has saved me a step for times when I do this.

But I still have no way to search for a 10 character string that may contain tildes in it somewhere.

Just to re-iterate - I'm not looking to permanently convert the tildes as the source data is elsewhere and will keep coming that way. Sure converting may be a workaround but it causes me knock on problems.

**HighlandScott** · 01-22-2009, 01:14 PM

Originally Posted by DonkeyOte

Could you post an example of what you're trying to achieve... I'm pretty sure you can do what you want without altering your data.... though I may be wrong... (it's not exactly unheard of!)

I definitely don't want to alter the data, just search and locate.

Example of 2 columns from file:
Job Number Job Title
ED3CSW~630 Children's Services Worker
ED3CSW~590 Children's Services Worker
ED3CSW~631 Children's Services Worker
ED3CSW~632 Children's Services Worker
ED3CSW~633 Children's Services Worker
ED3CSW~470 Children's Services Worker
ED3LSA~078 Learning Support Auxiliary
SW3LSCO630 Locum Service Co ordinator
ED1NT~~001 Nursery Co-ordinator Teacher
ED3NT~~509 Nursery Co-ordinator Teacher
ED3NT~~507 Nursery Co-ordinator Teacher
ED3NT~~508 Nursery Co-ordinator Teacher
ED3NT~~003 Nursery Co-ordinator Teacher
ED3NT~~001 Nursery Co-ordinator Teacher
ED3NT~~002 Nursery Co-ordinator Teacher
ED3NT~~005 Nursery Co-ordinator Teacher

I need a way use Find (Ctrl+F) to search for a Job Numer in a spreadsheet. If you type in, for example, ED3CSW~630, it will tell you "Microsoft Excel cannot find the data you are looking for....".

Workarounds I can use (or at least ones I know of) are to enter the characters in the string up to the point where there is a ~. For example I can search for ED3CSW and click Find All - then scroll down until I have the exact match I'm looking for.

**DonkeyOte** · 01-22-2009, 01:20 PM

So you're what - trying to execute a VLOOKUP for a particular Job Number to return the adjacent value from B ?

If we assume:

Please Login or Register  to view this content.

Then the following in E1 may work for you ...

Please Login or Register  to view this content.

**HighlandScott** · 01-22-2009, 01:24 PM

No, maybe I'm being unclear, but I'm not neccessarily trying alter, replace, lookup or anything like that. I just want to search for a Job Number and have Excel ACTUALLY LOCATE IT!! Instead, because of a tilde in the string, it tells me the data does not exist...but it DOES EXIST!

**DonkeyOte** · 01-22-2009, 01:28 PM

Same rules apply... for every tilde in the value you want to find you must "double up", eg:

To Find: ED1NT~~001

Enter: ED1NT~~~~001

Should work...

**HighlandScott** · 01-22-2009, 01:34 PM

Originally Posted by DonkeyOte

Same rules apply... for every tilde in the value you want to find you must "double up", eg:

To Find: ED1NT~~001

Enter: ED1NT~~~~001

Should work...

Perfect. That does work

Problem solved.

I'll likely be back in the next day or 2 no doubt with related errors trying to do a VLOOKUP on such strings

**DonkeyOte** · 01-22-2009, 01:35 PM

OK... well regards lookups etc see my earlier post... or remember the rule of doubling up... eg:

=VLOOKUP(SUBSTITUTE(D1,"~","~~"),$A$1:$B$27,2,0)

where D1 contains the string to find...