Email:      Pass:    Pass?
Close Window   
Free Ebook
Get Your Free Excel
ebook!
Our Top 15 Excel Tutorials
Instant Access!
E-mail:
Subscribe for Free Excel tips & more!
E-mail:
Advertisements



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
Code:

 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 ...
Remove Spaces Between Text in Excel
How to remove spaces from the middle of text in Excel.  This includes removing all spaces from the middle of text i ...
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 & ...

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







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)



Hi.

This seems simple enough but I can't seem to find the reference in office.microsoft.com or techonthenet.com.

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,
=CONCATENATE(TEXT(INT(ABS(A1)),0),CHAR(32),TEXT(INT(MOD(A1,1)*60),0),CHAR(32),
TEXT((MOD(MOD(A1,1)*60,1)*60),0),CHAR(32),IF(A1<0,"S","N"),CHAR(32),TEXT(INT(ABS(B1)),0),CHAR(32),
TEXT(INT(MOD(B1,1)*60),0),CHAR(32),TEXT((MOD(MOD(B1,1)*60,1)*60),0),CHAR(32),IF(B1<0,"W","E"))

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.

Maki

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?

HELP!

Many thanks

Hi,

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

cheers,
Roland


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.


Hello All,

Does anyone know how to simplify my code below?

Code:

=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:

VB:

 
=CONCATENATE(A7,B7,C7,CHAR(13),CHAR(10),D7,E7,F7,CHAR(13),CHAR(10),G7,H7,I7,CHAR(13),CHAR(10)) 


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

Examples

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!



Hello,

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.

Code:

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
    Else
       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:

Code:

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?

Thanks




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: http://www.excelforum.com/excel-prog...wildcards.html 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:

Code:

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)

=REGEX.FIND(A1,"(30[0-9]{4}-[0-9]{2})")

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

Code:

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

Code:

303429-28
303429-281


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

=REGEX.FIND(A1&IF(LEN(A1)<10,0,""),"(30[0-9]{4}-[0-9]{3})")

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

TIA
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
2-text2
3-text3

If cell2 is blank it will be the like:

Summary Cell
1-text1

3-text3

this is my formula:
"=E.Data!X2&CHAR(10)&E.Data!AQ2&CHAR(10)&E.Data!CK2&CHAR(10)&E.Data!DE2&CHAR(10)&E.Data!EH2&CHAR(10)&E.Data!FY2&CHAR(10)&E.Data!GQ2&CHAR(10)&E.Data!HD2"

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.


Alex



Hi,

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"


=IFERROR(LEFT(C2,FIND("_reg_",C2)-1),IFERROR(LEFT(C2,FIND("[",C2)-1),IFERROR(LEFT(C2,FIND("shiftflop",C2)-1),C2)))


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!

=IFERROR(LEFT(C2,FIND("shiftflop",C2)-1),C2)))

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:


=IFERROR(LEFT(A1,SEARCH({"[","_reg_","shiftflop"},A1)-1),A1)

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.


Thanks

Hi,

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
Code:

 
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?

Thanks

Simon


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?

Thanks

Karen


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

Code:

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.

Code:

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.

Code:

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
                Else
                    Err.Clear
                    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
            Else
                Exit Sub
            End If
        End If
    Case 3, 6
        If Char Like "/" Then Exit Sub
    End Select
    Beep
    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.

Regards
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!

Tom


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?


Hi!
I cannot find how to resolve my problem..

If I have to read, and save inside a string, the content of a cell which contains more than 256 char, for example using:
Code:

strStart = strStart + Format(ActiveCell.Offset(0, 83).Value, "General Number")


the content is truncate at 256.

How can I solve this excel limit?

Thankyou so much for your help.

dk


I am posing here with my head hanging low, as I usually can work out a complicated formula, but this one has me stumped. I have searched the internet for a formula to count with Base 26 (like how Excel counts the columns x, y, z, aa, ab, ac....etc.) and only found VB code, and one horribly clunky grid counter. I've spent about 12 hours on this and have it very close to working. The usefulness of this, is that the Base 26 is the shortest way to give a unique identifier to something in a list. I want it short, because these will be printed on very small objects and I don't want to use bar-codes.

The parameters are only that the original "A" be in caps; that the counting go up to ZZZ; and that there are no blank cells between "A" and your final line you want to count. So, if cell A1 you typed "A", then if you put this formula in cell A2, you would get "B".

Here is my formula for the first digit which works fine from "B" through "AA": =IF(L28="Z","AA",CHAR(CODE(RIGHT(L28,1))+1))

Here is my formula for the second digit which works fine from "B" through "ZZ":
=IF(AND(LEN(O25)=2,RIGHT(O25,1)="z"),CHAR(CODE(MID(O25,1,1))+1)&"A",IF(LEN(O25)=2,MID(O25,1,1)&IF(O25="Z","AA",CHAR(CODE(RIGHT(O25,1))+1)),IF(O25="Z","AA",CHAR(CODE(RIGHT(O25,1))+1))))

Here is my final formula which goes from "B" through "AAZ":
=IF(AND(LEN(P29)=3,MID(P29,2,1)<>"z"),MID(P29,1,1)&IF(P29="ZZ","AAA",IF(AND(LEN(P29)=2,RIGHT(P29,1)="z"),CHAR(CODE(MID(P29,1,1))+1)&"A",IF(LEN(P29)>=2,MID(P29,2,1)&IF(P29="Z","AA",CHAR(CODE(RIGHT(P29,1))+1)),IF(P29="Z","AA",CHAR(CODE(RIGHT(P29,1))+1))))),IF(P29="ZZ","AAA",IF(AND(LEN(P29)=2,RIGHT(P29,1)="z"),CHAR(CODE(MID(P29,1,1))+1)&"A",IF(LEN(P29)>=2,MID(P29,1,1)&IF(P29="Z","AA",CHAR(CODE(RIGHT(P29,1))+1)),IF(P29="Z","AA",CHAR(CODE(RIGHT(P29,1))+1)))))

I am confident that the process is right, it's just that this final digit's variables are too many for my brain to chase out. Hopefully you all can show me the way!

To help ease of data entry through a Terminal Emulator program, in Excel I concatenate multiple cells in a row and place an or Char(13), because that is the expected flow of data by the end program through the Term Emulator.

The problem is when I concatenate using the or CHAR(13), it surrounds the entire string with quote marks. I would rather it didn't.

To make it easy:

Type(copy) this formula into a cell in Excel, then copy that cell and paste it into Notepad(this doubles as the Terminal Emulator).

Code:

="A1234"&CHAR(13)&"B2345"&CHAR(13)&"C3456"


You will see that what is pasted into Notedpad is: (I have the ^ symbol to show for the CHAR(13), a box that appears in Notepad)

Code:

"A1234^B2345^C3456"


The string begins & ends with a "(quote). To get it to work with my Term Emulator software, I then need to do a Replace " with null inside Notepad. Then I re-copy from there and paste into the Term Emulator. I'd rather not have to go through this step.

btw, this is the way it is with any CHAR() character, not just the . This of course does not happen if you are concatenating cell, but do not make use of the CHAR().

How do I get Excel to get rid of the "(quotes) before I paste?




I have a formula which does the following: it finds the last comma followed by a space (, ) in a text string and replaces it with the word "and", and if there isn't a comma followed by a space, it leaves the text string as is.

For example, I have the following words "dog, cat, boy, girl", and this formula changes the text to "dog, cat, boy and girl" as long as there is at least one comma followed by a space.

That formula looks like this: =IF(LEN(B123)-LEN(SUBSTITUTE(B123,",", ""))>=1,IFERROR(TRIM(SUBSTITUTE($B123,","," and",LEN($B123)-LEN(SUBSTITUTE($B123,",","")))),$B123),TRIM(B123))

Here is the addition I want to make:

If there is exactly one comma followed by a space in a text string, it finds that comma and space and replaces it with the word "and". That formula would look like this:

=IF(LEN(B123)-LEN(SUBSTITUTE(B123,",", ""))=1,IFERROR(TRIM(SUBSTITUTE($B123,","," and",LEN($B123)-LEN(SUBSTITUTE($B123,",","")))),$B123),TRIM(B123))

However, I'd like to add to that if there are TWO or greater commas followed by a space, it will find the last comma with a space and replace it with ", and" [a comma, space and the word "and"].

The other formula I have that I need to make this adjustment to which basically does the same thing is this: =IFERROR(SUBSTITUTE(B123,", "," and ",LEN(B123)-LEN(SUBSTITUTE(B123,", "," "))),B123)

But I have the same problem... If there are more than two commas followed by a space, I need it to replace the last ", " with ", and". If there is only one comma followed by a space, it replaces the ", " with " and". And if there are no commas followed by a space, it leaves the text as it is.

Thank you!