character find in a cell

[Luis939]

how do i take a cell and search every character within its text until i find a comma (,) and then take every character before that comma and copy it into another cell, i dont know whether to use a loop of some kind, but ive always dealt with whole cells, not individual characters in a cell, thanks

 

[TroyMcClure72]

Something like this would work:

Dim strVal As Integer
Dim i as Integer
' Original cell - amend as necessary
strVal = Worksheets(1).Cells(2, 2).Value
i = 1

Do While Mid(strVal, i, 1) <> &quot;'&quot;
i = i + 1
Loop

' new cell
Worksheets(1).Cells(2, 3).Value = Mid(strVal, i + 1, Len(strVal))

 

[SkipVought]

Actually, you can do that with spreasheet functions.

If you text is in G16 then...

=LEFT(G16,FIND(",",G16)-1)

Hope this helps Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[Loomah]

Or the other code version of the no code version

Left(Range("a1").Text, InStr(Range("a1").Text, ",") - 1)

will get the desired characters

;-) If a man says something and there are no women there to hear him, is he still wrong?

 

[Luis939]

hmm well with loomah's code, what if i want to take the text before the comma, but put it in the cell next to it, i dont want to use absolute referencing, i want to be able to visit all cells in any column, then find the text before the comma and put it to the cell to the left of it, how can that be done, thanks

 

[Luis939]

well it doesnt have to be that complicated i just want to put the contents of any cell i encounter in cell A1, instead of the cell next to it, thanks

 

[Loomah]

Which is it to be??!!

Range(&quot;A1&quot= Left(activecell.Text, InStr(activecell.Text, &quot;,&quot - 1)


will do the second thing but you'll have to fire the code somehow. I'd suggest looking into selefction_change event but that will be a bit uncontrolable on it's own.

For your first option look at referencing the range using cells(row,col) something like Troy's suggestion. It's all down to how you ref.

Sorry can't be more specific at the mo but have to rush off to whip the Gooners

;-)
If a man says something and there are no women there to hear him, is he still wrong?

 

[Luis939]

just one more thing...i have the code
ActiveCell.Value = Left(ActiveCell.Text, InStr(ActiveCell.Text, &quot;(&quot - 1)

what must i do to say leave the text to the left the character before the (

I have something like New York, NY (ID1), when i run that line i get New York, NY_, i get that extra space, but i dont want that extra space between &quot;Y&quot; and the &quot;(&quot;, and i cant use &quot; &quot; in that line of code because I will only get New York as my result wihtout the , NY.
So what should i do to that code so that i dont get the second space included in my result, thank you

 

[Luis939]

nevermind, i got it already thanks

 

[Loomah]

I dunno what your solution is but I'd consider using 'InStrRev' as it searches from the end of the string rather than the begining - just incase you're interested, or anyomne else for that matter!!

Happy Friday
Terry for England!
;-) If a man says something and there are no women there to hear him, is he still wrong?

 

[Luis939]

Well thanks for that info, i didnt know there was a inStrRev function, maybe I could use that in the future, but what i simply did was instead of telling it to search for &quot;(&quot;, i changed it to &quot; (&quot;, in that way my resulting string wont have that unwanted space at the end of it, thanks