Email:      Pass:    Pass?
Close Window   
Free Ebook
Get Your Free Excel
Our Top 15 Excel Tutorials
Instant Access!

Subscribe for Free Excel tips & more!

Free Excel Forum

How To Make .find To Find String With Space Char?

Forum Register
Search Excel Forum Posts, Tutorials, Macros, Tips, and More

Hi, everyone!

I have written something like this

 toSearch = TextBox1.Value
 b = ActiveSheet.Range("A1:A50").find(toSearch)
 msgBox b.value

I have a text box on form where you can input text to search. Everything works fine untill you enter something with space char.
How is it possible to make Find method to find string with a space char inside?

Thanks in advance

View Answers     

Similar Excel Tutorials

Quickly Replace A Lot of Data in Excel
The Find & Replace works much the same way as Find and is located in the same place. (Find & Select drop do ...
How to Find and Fix Errors in Complex Formulas in Excel
Here, I'll show you a quick, simple, and effective way to fix formulas and functions in Excel. It can be hard to f ...
How to Quickly Find Data Anywhere in Excel
Finding specific records and/or cells is easy when using the Find tool in Excel. It is located within the Find & ...
Quickly Find All Comments in a Spreadsheet in Excel
In Excel, you can create a comment for any cell in the worksheet. The problem is that these comments are hard to f ...

Helpful Excel Macros

Macro to add a New Line to Message Box Pop-up Windows in Excel
- This is a very simple Message Box, pop-up window, macro for Excel that illustrates how to put new lines, the same thi
Vlookup Macro to Return All Matching Results from a Sheet in Excel
- This Excel Macro works like a better Vlookup function because it returns ALL of the matching results. Run the
Excel Macro that Searches Entire Workbook and Returns All Matches
- This is the ultimate Lookup Macro for Excel. It will search every worksheet in the workbook and return all of the mat
Complete Guide to Printing in Excel Macros - PrintOut Method in Excel
- This free Excel macro illustrates all of the possible parameters and arguments that you can include in the PrintOut Meth
Vlookup Macro to Return All Matching Results and Stack them with Previous Results
- This is very similar to the other Vlookup type Macro in that it returns all of the results that match a particular se

Similar Topics

Hi, i've data in (A1,A2,A3):
'a cd ' (abcd+space). five char
'fghij' (just fghij). five char
'klm ' (klm+2 space). five char
' no ' (space+no+2 space). five char

and i want only five characther only, if last is space, then only 4, and so on :
=A1, result just 'a cd' only 4 char (a+space+cd)
=A2, result just 'fghij' only 5 char (fghij)
=A3, result just 'klm' only 3 char (klm)
=A4, result just ' no' only 3 char (space+no)

remember, just last space doesn't count.

using excel 2003 at xp.

Tq bfore.

It's the CHAR() function. I don't really understand why, but just now I was working on a spreadsheet where it was being used in a long SUBSTITUTE formula. Every time I changed any precedent cell or the cell containing CHAR() Excel would crash, recover, crash again, and then bring up the spreadsheet unuseable, with all cells that used to contain CHAR() being returned as =#N/A.

So, I did a find/replace and changed out the CHAR with a hard-coded string literal - and now it works.

Anyone else experience this?

Formula is to determine the number of weekdays, based on a comma-separated list of "days off". Original, crashing:

=NETWORKDAYS($B$22,$D$22)-SUM(IFERROR(--(WEEKDAY(DATE(YEAR($B$22),MONTH($B$22),MID($F$22,IFERROR(FIND( CHAR(150) ,SUBSTITUTE($F$22,",",CHAR(150),ROW(1:31)-1))+1,1),IFERROR(FIND(CHAR(150),SUBSTITUTE($F$22,",",CHAR(150),ROW(1:31))),FIND(" = ",$F$22,1))-IFERROR(FIND(CHAR(150),SUBSTITUTE($F$22,",",CHAR(150),ROW(1:31)-1))+1,1))),2)


This seems simple enough but I can't seem to find the reference in or

How do you put the format of TEXT(value, format) to place "0" before a digit?

I'd worked out the formula to convert decimal GPS co-ordinates to sexagesimal but following is the format required
17 33 02S 140 39 03E

and, for the life of me, I can't figure out how to put 0 in front of a digit!

This is how I did the conversion bit,

where latitude is in cell A1 and longitude, B1

I tried putting 00 for format (e.g. TEXT(INT(ABS(A1)),00) but Excel automatically knocks it back to single 0.

Thanks for your help.


I have a list of outbound postcodes, some which are 2 char length (eg B1), some 3 (eg BA1 or G13) char, and some 4 char (eg LE16).

I need to update the list so that all entries are 4 char length, but the spaces need to be added in teh middle of the string, for example:

B1 becomes B 1 "space""space"
BA1 becomes BA 1 "space"
G13 becomes G 13 "space"
LE16 stays as LE16

Does anyone have any pearls of wisdom which formula would add the spaces in teh appropriate locations?


Many thanks


Using VBA, what is the best method to find the text string in cell a1 untill the first space?

for example A1 contains
/M50 920116

I like this to become /M50

the lenght of the string is variable


I'm trying to figure out what is the different between these two cells. They look identical to the naked eye. Same # of characters etc. The only difference I can see is a space so I'm trying to see what character that is.

Is there way to get the ascii equivalent. Basically I want to do the reverse of the Char() command. I want to find out what that ascii # is at a given space.

Hi All,

Below is the following code that I have used to pull in some data from another sheet.



Now if I add another cell lookup onto it like so:



It comes back with a #REF error, has any one any ideas.


Hello All,

Does anyone know how to simplify my code below?


=IF($A$1:$A$65536,"jessica simpson",b1&CHAR(10)&b2&CHAR(10)&b3&CHAR(10)&b4&CHAR(10)&b5)

Is there a way to include all string from b1:b100 without having to do b1&CHAR(10)&b2&CHAR(10)...all the way to b100? Thank you.

Fellow Forum Members,
I'm using Excel 2007. And I need to insert carriage returns into Excel formulas. Through reading some posts in this forum I learned it can be done by using CHAR(13) and CHAR(10). My working formula is shown below:



If you like these VB formatting tags please consider sponsoring me in support of injured Royal Marines

The problem with this formula happens when I paste the concatenated data into either NotePad++ or plain old Windows NotePad. The carriage return code does deliver on one thing. I get three text lines which is what I need. However, I'm also getting unwanted "quotes" at the beginning and end of the text lines.

A simple find and replace would take care of these unwanted quotes. But this is not an option for me because most of my concatenated data already contains "quotes". What variation of carriage return code can I use to eliminate the unwanted "quotes" when I paste into NotePad++? Any help will be greatly appreciated.

I need to find data in two different formats within a column


Webb Christopher

Greer Nancy

I need to find cells with text that have more than one space between the first and last names; and cells with text that have only one space between the first and last names.

I am fairly new to excel. I can find the space after the first word but cannot tell it to find ones that say have a space then another string of text.

Thank you for your help!


My question is about converting a Literal String to Character Code.

Here's why. I'm using the following coded InputBox. And it prompts the user for what characters to search for in a string.


Sub FindChar()

    CharToFind = InputBox("Please enter char to search for")

    Result = InStr(1, Text, CharToFind, 1)

    If Result <> 0 Then
       MsgBox "The char was found in the text at character number " & Result
       MsgBox "The char was not found."
    End If
End Sub

The above code works great if I enter a letter or an integer. But I need to allow the user to enter carriage returns in ANSI code, such as Chr(13).

As of now, if the user enters "Chr(13)" then the CharToFind variable actually contains the literal string in quotes, not the character code without the quotes. So that the final search string is essentially what's shown below:


Result = InStr(1, Text, "Chr(13)", 1) ' with quotes

' When what i want it to represent is:
Result = InStr(1, Text, Chr(13), 1) ' without quotes

So my question is, how do i convert the variable CharToFind's value of "Chr(13)" to a value of Chr(13)?

Is there a common way of doing this? A function, perhaps, that does this sort of conversion? I would be grateful for some direction in my dilemma.

Thanks for any and all help!

Hello All,

I want a function which returns the number of occurances of a given charcter in a string.

For example,

Given the string "hello" and the search char "e" we would get 1
Given the string 'hello" and the search char "l" we would get 2

Does this exist ? Or am i going to have to create this function?


Hi. I'm working on a Macro to take strings from a worksheet and insert them into an Outlook email. The Macro is working fine except for one thing.

I'm creating a bulleted list of text that I'm pulling from my worksheet. To create the bullet in the email, I used a bullet Dim using Char(149) in my macro and concatenate it with a string from my worksheet. I need the bullet and the string to be separated by several spaces. If I insert spaces between the bullet and the strings, in both my code, and my worksheet, the spaces do not show up in my email.


I want:
"Bullet" Text from the excel page.

I get:
"Bullet"Text from the excel page.

I'm using the simple string below to create my bullet (ascii char 149)
I've tried adding spaces (char 32) but the spaces don't show up in the email.

Please Login or Register  to view this content.

I give up on trying to create legitimate spaces in my outlook email. What I now want to do is take the easy way out by just adding a white font word to my bullet string, so it would be a bullet & "WORD" in white, giving the appearance of a space in the email.

Or, if anyone knows how I can add spaces, that would be amazing.

I'm having trouble formatting the font color of the word in this string. Can anyone help? Greatly appreciated.

I have a macro in my workbook that cleans, and sorts a paste from another workbook.

Part of this macro is supposed to remove any CHAR(32) and replace it with a space.
After running the macro, there are a few cells with CHAR(32) remaining.

For example, a CODE check on one particular cell reveals the following:
(Reading cell from RIGHT to LEFT) : 101 .... 116 .... 97 .... 116 .... 83 .... 32 .... 111 .... 103 .... 101 .... 105 .... 68 .... 32 .... 110 .... 97 .... 83

If I try to EDIT the cell, and REPLACE [Find what: CHAR(32) ] with [Replace with:" "] --- Excel tells me that it cannot find a match.
(, using Chr(32) in the [Find what:] field, doesn't work, either).

Here is the section of the macro that is supposed to remove CHAR(32):

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Selection.Replace What:=Chr(32), Replacement:=" ", _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
On Error Resume Next
For Each cell In Intersect(Selection, _
Selection.SpecialCells(xlConstants, xlTextValues))
cell.Value = Application.Trim(cell.Value)
Next cell
On Error GoTo 0
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True

There are three other incidents that are removed with similar code within the same macro: ..... CHAR(160)..." U"...and "(N)".
Each of these incidents are successfully removed.

Clearly, there are TWO occurrences of CHAR(32) in the cell mentioned, above.
Why isn't Excel recognizing these characters...?? ....

Following text is in cell:
e:\Copies O - V\Files\Company\ex-supplier\exhausts\Audi\A3 8L 96-5, 03\041-HR_Simons_Exhaust_Audi_A3_8L_rostfritt_stainless.jpg

I want function which takes text out from x:th char to next x:th char. Ie: =takeout(7;"\";8)
Function finds 7th "\" and takes text between seventh "\" and 8ht "\". Result would be: A3 8L 96-5, 03.
=takeout(6;"\";8) -> Audi\A3 8L 96-5, 03
I have tried to find solution all morning but I don't get it. I'm quite bad with DIY functions.

In relation to thread: and my very first question...

First let me start off by stating as clearly as possible that this thread is concerned solely with the creation of a Regular Expression Pattern ... I am not looking for "alternative" non-REGEX methods.

What I want to do is build a REGEX pattern that will establish as to whether or not a string matching a specified pattern can be found in another cell... the string rules are such:


String may be 9-10 chars in length
Chars 1-2: 30
Chars 3-6: must be numeric
Chars 7: must by hyphen
Chars 8-9/8-10: must be numeric (where 10 Chars present)

Thus far I came up with the below (where A1 holds string of interest)


Now this is all fine up to the point of the last character... ie the variable 10th Char... given it may or may not exist and where it does it may or may not be numeric.

I need to work out how to alter the Expression to look for a numeric 10th Char but only where Char 10 exists... ie if I change the {2} to {3} in the Expression that FIND will fail as you would expect whereas in reality if Chars 8 & 9 are numeric and no Char 10 exists the FIND should return 1 given it is a valid "ending" for the string in question...

Sample values for which REGEX.FIND should return 0


303429-28 X  ... ends in X
 31 3429-28 ... fails to start with 30
30 342 -28 ... insufficient numbers (5 digits)
303421- X 28 ... starts with non-numeric post hyphen
303421- 1  ... insufficient numbers post hyphen

Sample values for which REGEX.FIND should return 1



I guess I "could" cheat and alter the source String if < 10 chars and append a 0 to the end, ie:


but I'm guessing there must be a way of doing this using the pattern itself... I'm keen to learn!

The Donkey

Hello Everyone,

I have text comment entered on different cells on a worksheet from an user form on the last worksheet (Summary) I have the following form to concatenate those cells but entered on different lines and work perfectly if all the cells has text, when one of the cells is blank when it gets concatenate it give me an empty row which I want to remove. something like this:

cell1: text1 cell2: text2 cell3 text3 to

summary cell
1- text1

If cell2 is blank it will be the like:

Summary Cell


this is my formula:

and I found this one that do what I want but I don't know how to combine them together, I've been trying for two days now.

=SUBSTITUTE(SUBSTITUTE(TRIM(SUBSTITUTE(SUBSTITUTE(CONCATENATE(A1,"^",A1,"^",B1,"^",C1,"^",D1,"^",E1,"^",F1,"^",G1)," ","#"),"^"," "))," ",", "),"#"," ")

Any help or suggestion is greatly appreciated

Thank you in advance for your time.



I have a column of data - containing text strings.
The text strings are very similar (start and middle of strings are identical) but are unique due to characters at the end of each string.
I would like to truncate the text strings - by finding the last "common" sub-string - which I specify - and use LEFT to return the remainder.

The strings that I want to search for are
1) "_reg_"
2) "["
3) "shiftflop"


This works for the first 2 cases - but NOT the last.
If I remove the first 2 and ONLY include the last it works fine!


Is there an error in the formula or a limitation when "nesting" like that ?
Is there any issue including the char "["

I also tried using search function:


I believe that SEARCH({"A","B","C"},A1)-1) is equivalent of
search for any of the letters A,B OR C in cell A1 and return the cell position for the fist match you find.
Is that correct?

Again this worked for some cases but not all.

I am not sure what the correct syntax is - so looking for some help.



I am having trouble trimming a trailing space from a string that I have extracted from a cell as part of a macro.

I have looked at different options and the one that seems to be the best is the Trim function, in particular, the RTrim. Here is the code that I have written

Sheets("Projected Time").Select              ' selects the correct worksheet 
CellRange = ("G" & RangeNum)                 ' This is part of a loop so the RangeNum var changes with each iteration  
Range(CellRange).Select                         ' Selects the cell where I want the text from 
TrackAcc = ActiveCell.Value                    'Assigns the contents to the variable TrackAcc 
MsgBox (TrackAcc & ".")                         'This has been to allow me to test the value while debugging 
TrackAcc = RTrim(TrackAcc)                   'This should trim any trailing spaces 
MsgBox (TrackAcc & ".")                         'This has been to allow me to test the value while debugging 

The string values in the cells very in length from 2 to 4 chars (eg EF or ABDC) but always have 1 space at the end. In some cases there are spaces inside the string value (eg ST F or E F) and I need to keep those spaces.

When I run the above code, for the first messagebox I might get "BALT ." then the second message box appears with "BALT ." (exactly the same).

Anyone got any suggestions as to how I can do this?



I have some alphanumeric data imported from a Database. If the user hits the "enter" key when they type the text into this DB, I end up with a 'square' ie looks like CHAR(10) within the text.

If I use the Clean function it gets rid of it but I need to replace it with a space. I have also tried doing a replace, but can't "Find" the character

Does anyone have any suggestions?



I'm currently using the following code to concatenate several cells into one cell, but it also displays the cells that contain blanks.


Range("AJ11").Formula = "=AA11&char(10)&AB11&char(10)&AC11&char(10)&AD11&char(10)&AE11&char(10)&AF11&char(10)&AG11&char(10)&AH11&char(10)&AI11"

So I tried using the following code I found, but I'm getting an error.


Range("AJ11").Formula = "=SUBSTITUTE(MCONCAT(IF(AA11:AI11<>"",AA11:AI11&CHAR(10),"")),CHAR(10),"",COUNTA(AA11:AI11))"

I'm new to coding in VBA, so any help would be greatly appreciated.

I've copied one of Andrew Poulsom's previous post on this.. code below.. and i can't find the orignal post now to check... but i think i must have changed it wrongly..

what i want is the date format to be set to dd/mm/yy - and i swear it did but now

it allows an entry of 12/03/1 for example..

appreciate any help, as always Chuf.


Private Sub txtDat_Change()
    Dim Char As String
    Dim x As Date
    Dim y As Date
    Char = Right(txtDat.Text, 1)
    Select Case Len(txtDat.Text)
    Case 1 To 2, 4 To 5, 7 To 8
        If Char Like "#" Then
            If Len(txtDat) = 8 Then
                On Error Resume Next
                x = DateValue(txtDat.Text)
                y = DateSerial(Right(txtDat, 2), Mid(txtDat, 4, 2), Left(txtDat, 2))
                If Err = 0 And x = y Then
                    On Error GoTo 0
                    Exit Sub
                    On Error GoTo 0
                    txtDat.SelStart = 0
                    txtDat.SelLength = Len(txtDat.Text)
                    MsgBox "Please enter a valid date in the form dd/mm/yy", vbCritical + vbOKOnly, "Error"
                    Exit Sub
                End If
                Exit Sub
            End If
        End If
    Case 3, 6
        If Char Like "/" Then Exit Sub
    End Select
    On Error Resume Next
    txtDat.Text = Left(txtDat.Text, Len(txtDat.Text) - 1)
    txtDat.SelStart = Len(txtDat.Text)
End Sub

Hi All
I am trying to remove all traces of Char(31) from a column of data can someone give me an idea how to do it by Macro.
Sample text is this:
"Char(31)Subnet Mask " for columns "A" & "B"

BTW, it's the actual character, not the text Char(31).
OMG, I hope it's char(31), it's a little square.

Michael M

Hello all. My first post on this board.

I need to be able to find the last time a space is used in text, limited to sections of 26 characters at a time. In other words, I need to find the position of the last space in characters 1-26, 27-52, etc. I can find the FIRST one, of course, by using SEARCH and setting a start character. But, how do I find the LAST one?

Thanks so much for any help!


Dear Most Amazing Excel Masters,

Where can I find a complete list of all 255 CHAR function characters? I would like this list because when I enter the formula =CHAR(ROW()) and copy it down, there are many blanks. I know that =CHAR(10) means a hard line return in a formula, but by itself in a cell, the formula =CHAR(10) displays blanks.

Can you help me find the list that says whet all 255 characters are?