Free Microsoft Excel 2013 Quick Reference

Search for missing characters?

Hello everyone, thanks in advance if you can help-

Is there a way to search within cels for missing characters? I have a row of email addresses that I need to prune bad addresses out of, but what I need to search for is for characters that are missing; all email addresses need a "." and a "@" sign in order for them to be valid, and I need to delete bad addresses.

For example, john@excel.com is valid; johnexcel.com or john@excel is not, and I need to be able to search for cells missing those specific characters.

Is there a way?


Post your answer or comment

comments powered by Disqus
I need to get rid of the questions marks in the text on my sheet, but am unable to get my VBA code to search for an actual question mark and not the wildcard. I'm using the replace function as follows:


	VB:
	
wsReport.Cells = wsReport.Cells.Replace("?", "") 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
1. What's the trick to searching for wildcard characters?
2. Is there a slicker way to do this?

Thanks in advance.

Precede it with a tilde:
~?

Same would pertain to other wildcard:
~*
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================

"Ian" > wrote in message
...
Is there a way of searching the cells in a worksheet for the actual
character "?" since I don't want to use it as a wildcard but actually
search for the character itself within cells

Thanks in advance.

Is there a way of searching the cells in a worksheet for the actual
character "?" since I don't want to use it as a wildcard but actually
search for the character itself within cells

Thanks in advance.

Hi
Please assist

I have a list of 44,642 style no's of which there are certain styles containing special characters, the styles are made up of combination of letters and numbers.
e.g. of few styles CL00105, CL00109, CUBC278CME, CRXH3N01UTB5

Is there a formula i could use to search for special characters i.e. !,?,* and so on.

Your help will be greatly appreciated
Thank you
Ashraf

How do I search for a character in column 1 and display another character in column 2?

For example, let's say the cells in column 1 have these entries...
A1: blue, green, red
A2: red, yellow green,
A3: this is cool
A4: green eyes
A5: blue skies
etc...

I want a formula in column 2 that is something along these lines:
If cell A1 contains 'blue' then display 'awesome'

Thanks

Please can someone help me I am ready to crack.

I need to create a macro that can find missing numbers using VBA. I have already cracked it for a set of number running in sequence. Like this:

Sub Missing_Tuesday()
Dim i As Long
Dim Max As Long
Dim Min As Long
Dim s As String
Max = WorksheetFunction.Max(327)
Min = WorksheetFunction.Min(300)
For i = Min + 1 To Max - 1
If WorksheetFunction.CountIf(Range("f5:f43"), i) = 0 Then s = s & i & ","
Next i
MsgBox s

End Sub

However I need to search for missing numbers that now do not run in sequence. The list of numbers I need to search never change. They go 101,102, 203,204,105..... all the way up to 248.

As you can see using Min and MAX no longer workers. The numbers are always in the same row running from C3to C50.

How would I set up the search so that the result can be displayed in a message box.

If anyone can help you will be making me the happiest person in the world.

Thank you

I'm building a Macro that imports a .txt file into Excel and I've gotten really far. The only thing that's holding me up is that the the page breaks in the text file show up as non-printing characters (squares) and of course there's a header after each page break that I want to remove.

How do I search for the character indicating a Page Break so that I can have the macro delete it?

Hi,

The worksheet function SEARCH finds the first appearance of a character or
text string within another text string (or array of text strings). Does
anyone know a way to 'tweak' SEARCH to look for the Xth appearance of a
character. For instance, in

"Brian M. Johnson", I would like to search for the second "space" and have
my fuction return 9.

Your help is much appreciated.

Hello,

I am trying to create a macro to search for a character (in multiple columns) and return the values on the left and right of the character into seperate columns.

I want to search for the underscore "_" and return the two numbers on either side of the underscore into two different columns.

Quick Example:
BKN010 BKN015 OVC065 05_05 A2973 RMK AO2 UPE0552 SLP080 P0000 T00500050=
-DZ OVC009 05_05 A2970 RMK AO2 SLP071=
-RA BKN007 BKN025 06_06 A2969 RMK AO2 RAB1935DZE1852 SLP070 P0001 T00580058=

In two seperate columns put:
05 05
05 05
06 06
I have attached an example spreadsheet with what I would like to accomplish.

Thanks in advance,
Michael

I have an input box that asks the user for information. If the information is blank, it does one thing, if it has data in it, it does another. How do i search the inputted data for a specific character to make sure they are typing in the data correctly?

Here is an example of the code:

strNum = InputBox("Input box text")

            If strNum = "" Then
                Do stuff
                Else
            If strNum <> "" Then
                Do some other stuff
How would i set it up to search for a character within that code. Something like:

strNum =
InputBox("Input box text")

            If strNum = "" Then
                Do stuff
                Else
            If strNum Contains the following character "x" Then
                Do stuff
                Else
            If strNum <> "" Then
                Do some other stuff


Hello there!
Im trying to do a VBA code to look in an excel table one character, and I would like that each time the macro finds this character, will copy in other sheet the value that corresponds to it (that is four lines above). The character to search for is always in the column A.

Is there a way in excel for the program to only lookup the first 5 characters in the cell that you are getting the lookup data from?

Example...if a Cell read 12345678 I would only want to search for 12345 in a column with a VLOOKUP function

Hi all-

I have a spreadsheet with a long list of search engine terms starting in cell A3 going down. I am trying to create a column that will make a flag if a certain character string shows up in it. There are multiple strings I would like to check for and if any of those strings show up, I would like the cell to flag a "1", otherwise flag it with "0".

This is the formula I found would work for checking a single character string ("word"):

=IF(ISERROR(FIND("word",A3,1)),0,IF(FIND("word",A3,1)>=1,1,))

Is there a way to check for multiple strings or from a list of strings?

Let me know if this doesnt make sense.

Thanks,
Scott

Hi,
I have a list of file names including the complete path, and I'm trying
to see if the list has special characters like %, &, @ etc. I am using
the find function now but I have to have a new function to check all
the characters. Is there a way to have just one formula to check to
see a list like

C:WINDOWSIE4 Error Log.txt
C:WINDOWSKB832854.log

etc has any of the characters I'm searching for?

Thanks for your help.

Hi,
I have a list of file names including the complete path, and I'm trying
to see if the list has special characters like %, &, @ etc. I am using
the find function now but I have to have a new function to check all
the characters. Is there a way to have just one formula to check to
see a list like

C:WINDOWSIE4 Error Log.txt
C:WINDOWSKB832854.log

etc has any of the characters I'm searching for?

Thanks for your help.

Hi all. I apologize if this should be else where. I am using Excel 2007, but it might be the same across all versions.

I have a large excel sheet with a column containing the data below:

A
B
.
. .
Z

and

1
2
3
.
.
.
29

There are other columns associated to these and so the A-Z and 1-29 range repeats itself several times in one column.

Is there a way to tell excel to search a certain string (or range) on cells and have excel spit back, either by 1's and 0's or error messages, where a string is missing a number/letter in the link. So for example, the letter string is missing, say letter E? Or the number string is missing, say, # 6?

Thanks,
Ryan

VBA search for value and insert space two characters to the right

First of all a friendly hallo to all Forum members.

Ok I've got about 20000+ lines of data that look like this:

NODE S1 S2 S3 SINT SEQV
1 -0.90520E-02-1.3410 -2.1393 2.1302 1.8640
2 0.77851E-02-0.92696 -1.9901 1.9979 1.7314
3 -0.34783E-01-1.1249 -2.0717 2.0370 1.7655
4 0.36477E-01-0.93091 -2.0064 2.0429 1.7700
5 0.83489E-02-0.92708 -1.9901 1.9985 1.7319

And I want to insert a space behind "E-XX" so it will look something like this:

NODE S1 S2 S3 SINT SEQV
1 -0.90520E-02 -1.3410 -2.1393 2.1302 1.8640
2 0.77851E-02 -0.92696 -1.9901 1.9979 1.7314
3 -0.34783E-01 -1.1249 -2.0717 2.0370 1.7655
4 0.36477E-01 -0.93091 -2.0064 2.0429 1.7700
5 0.83489E-02 -0.92708 -1.9901 1.9985 1.7319

At the moment the VBA code looks somewhat like this:

No = 20000

For i = 1 To No

   Ra = "A" & i
   Range(Ra).Select

ActiveCell.Replace What:="E-01", Replacement:="E-01 ", LookAt:=xlPart, SearchOrder:=xlByRows,
MatchCase:=False
ActiveCell.Replace What:="E-02", Replacement:="E-02 ", LookAt:=xlPart, SearchOrder:=xlByRows,
MatchCase:=False
ActiveCell.Replace What:="E-03", Replacement:="E-03 ", LookAt:=xlPart, SearchOrder:=xlByRows,
MatchCase:=False
ActiveCell.Replace What:="E-04", Replacement:="E-04 ", LookAt:=xlPart, SearchOrder:=xlByRows,
MatchCase:=False
ActiveCell.Replace What:="E-05", Replacement:="E-05 ", LookAt:=xlPart, SearchOrder:=xlByRows,
MatchCase:=False
ActiveCell.Replace What:="E-06", Replacement:="E-06 ", LookAt:=xlPart, SearchOrder:=xlByRows,
MatchCase:=False
ActiveCell.Replace What:="E-07", Replacement:="E-07 ", LookAt:=xlPart, SearchOrder:=xlByRows,
MatchCase:=False
ActiveCell.Replace What:="E-08", Replacement:="E-08 ", LookAt:=xlPart, SearchOrder:=xlByRows,
MatchCase:=False
ActiveCell.Replace What:="E-09", Replacement:="E-09 ", LookAt:=xlPart, SearchOrder:=xlByRows,
MatchCase:=False
But unfortunately it has two major drawbacks. It is inefficient and thus quite slow and I have to edit the code every time I apply it to a new set of data depending on the number of lines.
So my ides for improvement are the following.

Find "E-" and insert a " " (space) two characters to the right of "E-"

and

No=(value in a specified cell)

Unfortunately my web based research hasn't been to successful and all problems I found that were similar to mine were solved with code that is way out of my novas league.

Any suggestions or help?

Thank you for your time!

the TUGuys

I have a number of project files that are maintained on the network.
These files are named "XXXXXX Project Name.XLS".
XXXXXX is the project number. This numbers are unique, so there is only one file per number within the directory. Project names are mostly unique, but sometimes they could be same for a specific site.
We recieve financial data in excel file format from home office. These reports reference only the project numbers, not the name.

I have created an excel program that extracts project numbers and related financial data from the report [from home office].

I would like to create a button or check box next to each project number in my program and link it to the respective file on our network. Once I access the local file on the network I could copy the financial data in its right place.

The problem is opening the file based on project number only, while the file names contain Project Name as well.

So, can I look up a file by only searching for first six characters of the name?

Or can the Hyperlink function work with some sort of wildcard? I can write the formulas to create the hyperlink and then write a macro to look for that file, but I need to be able to find and open the file by looking up only the first six characters.

Any help or suggestions appreciated.

modytrane.

Hi;

I have a two sheet workbook. One sheet allows for the entry of search
criteria in the upper half and the search results in the lower half. The
other sheet contains the records that are searched.

I have a situation where in one field called Location, there are similar
values such as Rack 1, Rack 11, Rack 12, etc. If I want to search for rows
that only have Rack 1 in the Location and I put in Rack 1 into my search
field, rows that have Rack 1, Rack 11, etc. come up as hits. Is there a
wilcard or other character that allows me search for a speciific value like
"Rack 1" ? By the way I tried putting Rack 1 in quotes and itr did not help.
Any assistance is greatly appreciated.
--
JJFJR

Hi Guys,

I have been searching for a formula which search set of characters by ascii code value in Range. For ex.

I have a rule for a range (C2:C50) that it should have A to C and it should not have D,E,...Z. And I would get a formula like below.


	VB:
	
0 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
So it will show result like below

"ABC" = FALSE
"ABD" = TRUE

It is alright if it is an array formula.

Advance thank you for your solution guys.

Thank you.

Udhaya_K

Maqbool and supporters,

I have attached an example VALIDATION.xls with desire formula sample. Please let me know if it is not clear.

Udhaya_K

Defeat Easy, Win Hard

Hi:

I wonder if anyone can help me.

I would like to test if a certain word exists in another cell. For example:

In cell A1 I have "The Big Black Cat"

In Cell B1 I have the word "Black"

I just want to put a formula in Cell C1 that will return TRUE since "Black" does appear in Cell A1. (And of course the formula would return FALSE, if it didn't).

I know about the FIND function, but this only appears to search for one character.

Is there another function I am missing, or a cunning trick?

Thanks!

MikeG

I am working on a spread sheet that searches for an employee's clock number, pulls the data from a particular week and displays it all on one page. I have 6 different tables of different stats, 4 weeks each and I would like to pull all of this data together. I have successfully created a page that when you plug in an employee's number, all of thier stats apear from all 6 data sets for the last 4 weeks. I do this by using

week 1
=IF(ISNA(VLOOKUP($E$1,Cards!$A$5:$H$100,2,FALSE)),"",(VLOOKUP($E$1,Cards!$A$5:$H$100,2,FALSE)))

week 2
=IF(ISNA(VLOOKUP($E$1,Cards!$J$5:$Q$100,2,FALSE)),"",(VLOOKUP($E$1,Cards!$J$5:$Q$100,2,FALSE)))

week 3
=IF(ISNA(VLOOKUP($E$1,Cards!$S$5:$Z$100,2,FALSE)),"",(VLOOKUP($E$1,Cards!$S$5:$Z$100,2,FALSE)))

week 4
=IF(ISNA(VLOOKUP($E$1,Cards!$AB$5:$AI$100,2,FALSE)),"",(VLOOKUP($E$1,Cards!$AB$5:$AI$100,2,FALSE)))

Credit CardsCustomersTotal AppsPer/1000week 13/8/2008192315.62week 23/15/200819315.18week 33/22/200813200week 43/29/200810519.52Totals62258.04

I have to account for data that is missing becasue they may not have worked, and then the devide by zero error. This works for me if I separate all 4 weeks into thier own cell, which is handy for the employee to see.

But I want to create a master employee sheet for the manager that combines 4 weeks into one cell. So vlookup up would collect all 4 values of total cards for the week and add them up to get 622. I might also want to devide by 4 to get the 4 week average in the same cell; but if the value is zero...

Maybe vlookup is the wrong function, but I haven't seen any other examples of what I am looking to have happen.

I have taught myself through till this point and 3 days of creating, I need some help for the final push. I've tried different combos and looked online... any suggestions?

I have tried using the following function but Excel '03 keeps giving me an error and I'm suspicious that I have too many arguments but it never explicitly says that.

IF(ISNUMBER(SEARCH("tacttc",A5)),(UPPER(MID(A5,(SEARCH("tacttc",A5,10)),10)))

I'm trying to search for 8 different 6 letter recognition sequences within entires in a particular column (A in the example above). I also need it to return so many characters after finding the recognition sequence which is the reason for the MID function.

If anyone has suggestions, I would welcome them all.
I could do this by eye, I'd go cross-eyed before I finished the data set.
Thanks

Hi all

Maybe you could help me with an Excel VBA question. I would like to search for a text string to check if it contains certain characters. I'd like to do this in VBA.

Tried using the FIND function, but couldn't get it to work in VBA, and couldn't figure out how to use it with more than one search character.

Any ideas?

Thanks in advance!


No luck finding an answer? You could always try Google.