Email:      Pass:    Pass?
Hi!
Close Window   
TE
Free Excel Tips in Your Email!
Join Over 20,000 Happy Subscribers!
E-mail:
Subscribe for Free Excel tips & more!
E-mail:


Advertisements


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
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 Video Tutorials

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)

I'm using "Evaluate" for the first time. For some reason it's not working and the macro jumps to the end. Can you look at my code and the attached file and tell me if I am using the term incorrectly? The macro is Sub ColNWShears() in Module-1.

The values currently in Col-N are the results I am writing the code to put there.

Code:

Sub ColNWShears()
'
' ColN Macro
' 12/23/2010 by Brad Hudiburg
'
Dim X, y, z, yRow, y2

'
' Sheets("SWSel").Select
    Range("N3").Select  '3
    
On Error GoTo EndIt
Start:

    X = ActiveCell.Address
    z = ActiveCell.Row

    ActiveCell.Offset(0, -1).Range("A1").Select
    
    ' if blank cell go down col to next value
    If ActiveCell.Value = "" Then
        Selection.End(xlDown).Select
        y = ActiveCell.Value
        yRow = ActiveCell.Row
        y2 = Range(Cells(yRow, 2).Address).Value
        MsgBox "y=" & y & "y2=" & y2 & ", yRow=" & yRow
    
    Else
'        y = ActiveCell.Value
    End If
   Stop
    Range(X).Select
    
    ' go to new row if skipping over nonused SWs
    ' if r[z]c[-12]: left of "-" = rc[-12] then cont to return to x
    ' else Range(x).Select and     ActiveCell.Offset(1, 0).Range("A1").Select

'    X2 = 1
    ' X2 = Evaluate("IF(LEFT(RC[-12],FIND(CHAR(1),SUBSTITUTE(R" & yRow & "C[-12],""-"",CHAR(1),1))-1)=" & y2 & ",1,2)")
    ' LEFT(RC[-12],FIND(CHAR(1),SUBSTITUTE(R" & yRow & "C[-12],""-"",CHAR(1),1))-1)
'    X2 = Evaluate("LEFT(RC[-12],FIND(CHAR(1),SUBSTITUTE(R" & yRow & "C[-12],""-"",CHAR(1),1))-1)")
    ' IF(ISERROR(LEFT(B3,FIND(CHAR(1),SUBSTITUTE(B3,"-",CHAR(1),1))-1)),"",  LEFT(B3,FIND(CHAR(1),SUBSTITUTE(B3,"-",CHAR(1),1))-1)  )
    X2 = Evaluate("IF(ISERROR(LEFT(B3,FIND(CHAR(1),SUBSTITUTE(B3," - ",CHAR(1),1))-1)),"",  LEFT(B3,FIND(CHAR(1),SUBSTITUTE(B3," - ",CHAR(1),1))-1)  )")
    MsgBox "x2=" & X2
    
   Stop
    ' MsgBox "x2=" & X2
    Stop
    If X2 = 1 Then
        MsgBox X2
        ActiveCell.FormulaR1C1 = "=rc[-7]*" & y
        ActiveCell.Offset(1, 0).Range("A1").Select
    Else
        MsgBox X2
        ActiveCell.FormulaR1C1 = ""
        ActiveCell.Offset(1, 0).Range("A1").Select
    End If
    
    
    'add if activecell.row < row(for y)-1 then add eqn again




    If ActiveCell.Value = "end" Then
        GoTo EndIt 'End
    End If
    GoTo Start

EndIt:
'MsgBox ActiveCell.Row
'MsgBox "ActiveCell.Value, y=" & y

End Sub





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


that character can be found by
=CODE(MID(A1,6,1)) in your example, where A1 contains "1029 · etc" -
(it's char(183). Then you can use LEFT(A8,FIND(CHAR(183),A8)-1)
if A8 has the text wanted.
Bob Umlas
Excel MVP

"Dennis" wrote:

> Using 2003
>
> Would like to use a mid() to obtain all information in the text string (a
> cell) up to the space before the "·" in (1029 · Money Market Acccount)
>
> Therefore, in this case my formula would return "1029"
>
> In another case my text is (1029-25 · Money Market Acccount)
>
> In turn, my desired text would be "1029-25"
>
> What combination of Find() or Mid() or what ever do I use to do the task?
>
> Initially, I must ascertain how to obtain the chr() value of
> the "·" character? No?
>
> Dennis



hi...can anyone help me with this zip-code formatting logic?

For US: The valid format is NNNNN-NNNN
- When US Zip Code length without '-' or ' ' is >5 and < 9 char then use
first 5 positions
- When Us Zip Code length is <5 chars then move space.
- When US Zip code is having alphabet, invalid and blank then move space
- When US zip code is having 9 char width then pass 5-4 format (99999-9999)
- When US Zip Code length without '-' or ' ' is >9 then use first 9 char.
- When Zip is empty or not valid, move '00000' to SAP

For CANADA: The valid format is CNC NCN
- When zip code length is >7 char, move first 7 char
- When zip code length is <7 char, move space
- When zip code is blank and invalid then move space.
- When Zip is empty or not valid, move 'A0A 0A0' to SAP

For London (county code GB):
- zip code must not exceed 9 characters (<=9 char). If >9 char then, move
first 9 char.)
- When Zip is empty or not valid, move '000000000' to SAP

For Other countries:
- Pass as it is.

I built a formula but it doesnt support the condition of having an alphabet
and "-" in US format. I would appreciate it if i cld get any help. thank u



Hi All,

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

Code:

=IF('2'!C4,"a",'2'!G4&CHAR(10))&IF('2'!C5,"a",'2'!G5&CHAR(10))&IF('2'!C6,"a",'2'!G6&CHAR(10))&IF('2'!C7,"a",'2'!G7&CHAR(10))&IF('2'!C8,"a",'2'!G8&CHAR(10))&IF('2'!C9,"a",'2'!G9&CHAR(10))


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

Code:

=IF('2'!C4,"a",'2'!G4&CHAR(10))&IF('2'!C5,"a",'2'!G5&CHAR(10))&IF('2'!C6,"a",'2'!G6&CHAR(10))&IF('2'!C7,"a",'2'!G7&CHAR(10))&IF('2'!C8,"a",'2'!G8&CHAR(10))&IF('2'!C9,"a",'2'!G9&CHAR(10))&IF('2'!C10,"a",'2'!G10&CHAR(10))


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

Thanks


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 am looking for non-printable characters where char(126)

I have this formula

=SUMPRODUCT((ISNUMBER(FIND("~",A1:A4)))*ROW(A1:A4))

That finds one piece of text in a range, but i would like to find other characters where Char(

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!


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.
(And...no, 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):

Range("A83:C168").Select
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...?? ....


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


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'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 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'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


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!

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?