Character Analysis
 

Hey Guys,

What I would like to do is break down the characters in a cell and analyze
them. The ultimate use for this is going to be because I want to copy all
the characters before the third space in a cell. I tried several things
using things similar to:

Cells(a,b).Characters(Start:=i, Length:=1).Select
character_check = Selection
If character_check = " " then
space_count = space_count + 1

and so on...

The code breaks every time the macro hits the first line in this section.
If i just could figure out how to check each character in a cell, i could do
the rest myself i think. Does anyone have any help on how to look at each
character of the cell and check what it is? Thanks for the help guys!

Character Analysis
 

Try this with the data in Cell A1

Sub Macro()
MsgBox Trim(Replace(Range("A1"), Split(Range("A1"), " ", 4)(3), ""))
End Sub


If this post helps click Yes
---------------
Jacob Skaria


"Derek Johansen" wrote:

Hey Guys,

What I would like to do is break down the characters in a cell and analyze
them. The ultimate use for this is going to be because I want to copy all
the characters before the third space in a cell. I tried several things
using things similar to:

Cells(a,b).Characters(Start:=i, Length:=1).Select
character_check = Selection
If character_check = " " then
space_count = space_count + 1

and so on...

The code breaks every time the macro hits the first line in this section.
If i just could figure out how to check each character in a cell, i could do
the rest myself i think. Does anyone have any help on how to look at each
character of the cell and check what it is? Thanks for the help guys!

Character Analysis
 

Umm... all this is doing is giving me an output of cell A1... That's not what
I'm looking for. What I need is to count the number of spaces in the cell
from the begining of the cell, and then when the 3rd space occurs, i will be
copying all the text before it. For example:

If I cell contains: "2 X 4 Lumber"

I want to identify the third space after the 4, and then copy all the text
from ahead of that space 2 X 4 and paste into a different location. COPY,
not cut, but I can deal with those types of semantics.

"Jacob Skaria" wrote:

Try this with the data in Cell A1

Sub Macro()
MsgBox Trim(Replace(Range("A1"), Split(Range("A1"), " ", 4)(3), ""))
End Sub


If this post helps click Yes
---------------
Jacob Skaria


"Derek Johansen" wrote:

Hey Guys,

What I would like to do is break down the characters in a cell and analyze
them. The ultimate use for this is going to be because I want to copy all
the characters before the third space in a cell. I tried several things
using things similar to:

Cells(a,b).Characters(Start:=i, Length:=1).Select
character_check = Selection
If character_check = " " then
space_count = space_count + 1

and so on...

The code breaks every time the macro hits the first line in this section.
If i just could figure out how to check each character in a cell, i could do
the rest myself i think. Does anyone have any help on how to look at each
character of the cell and check what it is? Thanks for the help guys!

Character Analysis
 

With "2 X 4 Lumber" in A1

Try the below macro.. which will copy 2 x 4 to Range("B1")


Sub Macro()
Range("B1") = Trim(Replace(Range("A1"), Split(Range("A1"), " ", 4)(3), ""))
End Sub

If this post helps click Yes
---------------
Jacob Skaria


"Derek Johansen" wrote:

Umm... all this is doing is giving me an output of cell A1... That's not what
I'm looking for. What I need is to count the number of spaces in the cell
from the begining of the cell, and then when the 3rd space occurs, i will be
copying all the text before it. For example:

If I cell contains: "2 X 4 Lumber"

I want to identify the third space after the 4, and then copy all the text
from ahead of that space 2 X 4 and paste into a different location. COPY,
not cut, but I can deal with those types of semantics.

"Jacob Skaria" wrote:

Try this with the data in Cell A1

Sub Macro()
MsgBox Trim(Replace(Range("A1"), Split(Range("A1"), " ", 4)(3), ""))
End Sub


If this post helps click Yes
---------------
Jacob Skaria


"Derek Johansen" wrote:

Hey Guys,

What I would like to do is break down the characters in a cell and analyze
them. The ultimate use for this is going to be because I want to copy all
the characters before the third space in a cell. I tried several things
using things similar to:

Cells(a,b).Characters(Start:=i, Length:=1).Select
character_check = Selection
If character_check = " " then
space_count = space_count + 1

and so on...

The code breaks every time the macro hits the first line in this section.
If i just could figure out how to check each character in a cell, i could do
the rest myself i think. Does anyone have any help on how to look at each
character of the cell and check what it is? Thanks for the help guys!

Character Analysis
 

This copied "2 X" and no last character.

This macro needs to also work for "2 X 6 " "2 X 8 ", "2 X 10 ", "2 X 12 "
This is why i simply want to search each cell for the 3rd space and copy
before. I don't want it based on the numbers at all...

"Jacob Skaria" wrote:

With "2 X 4 Lumber" in A1

Try the below macro.. which will copy 2 x 4 to Range("B1")


Sub Macro()
Range("B1") = Trim(Replace(Range("A1"), Split(Range("A1"), " ", 4)(3), ""))
End Sub

If this post helps click Yes
---------------
Jacob Skaria


"Derek Johansen" wrote:

Umm... all this is doing is giving me an output of cell A1... That's not what
I'm looking for. What I need is to count the number of spaces in the cell
from the begining of the cell, and then when the 3rd space occurs, i will be
copying all the text before it. For example:

If I cell contains: "2 X 4 Lumber"

I want to identify the third space after the 4, and then copy all the text
from ahead of that space 2 X 4 and paste into a different location. COPY,
not cut, but I can deal with those types of semantics.

"Jacob Skaria" wrote:

Try this with the data in Cell A1

Sub Macro()
MsgBox Trim(Replace(Range("A1"), Split(Range("A1"), " ", 4)(3), ""))
End Sub


If this post helps click Yes
---------------
Jacob Skaria


"Derek Johansen" wrote:

Hey Guys,

What I would like to do is break down the characters in a cell and analyze
them. The ultimate use for this is going to be because I want to copy all
the characters before the third space in a cell. I tried several things
using things similar to:

Cells(a,b).Characters(Start:=i, Length:=1).Select
character_check = Selection
If character_check = " " then
space_count = space_count + 1

and so on...

The code breaks every time the macro hits the first line in this section.
If i just could figure out how to check each character in a cell, i could do
the rest myself i think. Does anyone have any help on how to look at each
character of the cell and check what it is? Thanks for the help guys!

Character Analysis
 

Derek

You must be having more spaces in betwen X and 4. The below is not based on
any particular number.. Try with the below values in cell A1. It will extract
the 1st three

a b c d e f
1 2 3 4 5

Any way I have modified the macro to handle multiple spaces. Try and feedback

Sub Macro()
Dim strData As String
strData = WorksheetFunction.Trim(Range("A1") & " , ")
Range("B1") = Trim(Replace(strData, Split(strData, " ", 4)(3), ""))
End Sub

--
If this post helps click Yes
---------------
Jacob Skaria


"Derek Johansen" wrote:

This copied "2 X" and no last character.

This macro needs to also work for "2 X 6 " "2 X 8 ", "2 X 10 ", "2 X 12 "
This is why i simply want to search each cell for the 3rd space and copy
before. I don't want it based on the numbers at all...

"Jacob Skaria" wrote:

With "2 X 4 Lumber" in A1

Try the below macro.. which will copy 2 x 4 to Range("B1")


Sub Macro()
Range("B1") = Trim(Replace(Range("A1"), Split(Range("A1"), " ", 4)(3), ""))
End Sub

If this post helps click Yes
---------------
Jacob Skaria


"Derek Johansen" wrote:

Umm... all this is doing is giving me an output of cell A1... That's not what
I'm looking for. What I need is to count the number of spaces in the cell
from the begining of the cell, and then when the 3rd space occurs, i will be
copying all the text before it. For example:

If I cell contains: "2 X 4 Lumber"

I want to identify the third space after the 4, and then copy all the text
from ahead of that space 2 X 4 and paste into a different location. COPY,
not cut, but I can deal with those types of semantics.

"Jacob Skaria" wrote:

Try this with the data in Cell A1

Sub Macro()
MsgBox Trim(Replace(Range("A1"), Split(Range("A1"), " ", 4)(3), ""))
End Sub


If this post helps click Yes
---------------
Jacob Skaria


"Derek Johansen" wrote:

Hey Guys,

What I would like to do is break down the characters in a cell and analyze
them. The ultimate use for this is going to be because I want to copy all
the characters before the third space in a cell. I tried several things
using things similar to:

Cells(a,b).Characters(Start:=i, Length:=1).Select
character_check = Selection
If character_check = " " then
space_count = space_count + 1

and so on...

The code breaks every time the macro hits the first line in this section.
If i just could figure out how to check each character in a cell, i could do
the rest myself i think. Does anyone have any help on how to look at each
character of the cell and check what it is? Thanks for the help guys!

Character Analysis
 

Thank you! I think what fixed it was that you trimmed A1 before we evaluated
it. The problem before was that when I imported my data the other software
program had put a space BEFORE the 2. So it was picking up the third space
to be after the X instead of after the last number. I really appreciate the
help, and was wondering if you could give me assistance on one more thing:

Ultimately what I had planned to do with this was to eliminate the " X " and
just have "24" or "210" as an output (more will be added to this string, but
all i need from the current cell is that). Would the way to do this be to
use a split function with " X " as a deliminator? I think I could figure
that out if that is the most effecient way!

Again, thanks for your help, the macro works as desired :)

"Jacob Skaria" wrote:

Derek

You must be having more spaces in betwen X and 4. The below is not based on
any particular number.. Try with the below values in cell A1. It will extract
the 1st three

a b c d e f
1 2 3 4 5

Any way I have modified the macro to handle multiple spaces. Try and feedback

Sub Macro()
Dim strData As String
strData = WorksheetFunction.Trim(Range("A1") & " , ")
Range("B1") = Trim(Replace(strData, Split(strData, " ", 4)(3), ""))
End Sub

--
If this post helps click Yes
---------------
Jacob Skaria


"Derek Johansen" wrote:

This copied "2 X" and no last character.

This macro needs to also work for "2 X 6 " "2 X 8 ", "2 X 10 ", "2 X 12 "
This is why i simply want to search each cell for the 3rd space and copy
before. I don't want it based on the numbers at all...

"Jacob Skaria" wrote:

With "2 X 4 Lumber" in A1

Try the below macro.. which will copy 2 x 4 to Range("B1")


Sub Macro()
Range("B1") = Trim(Replace(Range("A1"), Split(Range("A1"), " ", 4)(3), ""))
End Sub

If this post helps click Yes
---------------
Jacob Skaria


"Derek Johansen" wrote:

Umm... all this is doing is giving me an output of cell A1... That's not what
I'm looking for. What I need is to count the number of spaces in the cell
from the begining of the cell, and then when the 3rd space occurs, i will be
copying all the text before it. For example:

If I cell contains: "2 X 4 Lumber"

I want to identify the third space after the 4, and then copy all the text
from ahead of that space 2 X 4 and paste into a different location. COPY,
not cut, but I can deal with those types of semantics.

"Jacob Skaria" wrote:

Try this with the data in Cell A1

Sub Macro()
MsgBox Trim(Replace(Range("A1"), Split(Range("A1"), " ", 4)(3), ""))
End Sub


If this post helps click Yes
---------------
Jacob Skaria


"Derek Johansen" wrote:

Hey Guys,

What I would like to do is break down the characters in a cell and analyze
them. The ultimate use for this is going to be because I want to copy all
the characters before the third space in a cell. I tried several things
using things similar to:

Cells(a,b).Characters(Start:=i, Length:=1).Select
character_check = Selection
If character_check = " " then
space_count = space_count + 1

and so on...

The code breaks every time the macro hits the first line in this section.
If i just could figure out how to check each character in a cell, i could do
the rest myself i think. Does anyone have any help on how to look at each
character of the cell and check what it is? Thanks for the help guys!

Character Analysis
 

Replaced X and spaces inbetween

Sub Macro()
Dim strData As String
strData = Trim(Range("A1")) & Space(3)
strData = Trim(Replace(strData, Split(strData, " ", 4)(3), ""))
strData = Replace(Replace(strData, "X", ""), " ", "")
Range("B1") = strData
End Sub


If this post helps click Yes
---------------
Jacob Skaria


"Derek Johansen" wrote:

Hey Guys,

What I would like to do is break down the characters in a cell and analyze
them. The ultimate use for this is going to be because I want to copy all
the characters before the third space in a cell. I tried several things
using things similar to:

Cells(a,b).Characters(Start:=i, Length:=1).Select
character_check = Selection
If character_check = " " then
space_count = space_count + 1

and so on...

The code breaks every time the macro hits the first line in this section.
If i just could figure out how to check each character in a cell, i could do
the rest myself i think. Does anyone have any help on how to look at each
character of the cell and check what it is? Thanks for the help guys!

Character Analysis
 

You are a HUGE help! I can't believe I didn't think to use a simple replace
function! I've only been working with VB Excel for three weeks now, and the
only other experience I have is a Fortran class I took last semester in
school! People like you are really a huge help for me to learn and create
code for my summer work situation! I really really appreciate it!


-----

Is the the most efficient/

"Jacob Skaria" wrote:

Replaced X and spaces inbetween

Sub Macro()
Dim strData As String
strData = Trim(Range("A1")) & Space(3)
strData = Trim(Replace(strData, Split(strData, " ", 4)(3), ""))
strData = Replace(Replace(strData, "X", ""), " ", "")
Range("B1") = strData
End Sub


If this post helps click Yes
---------------
Jacob Skaria


"Derek Johansen" wrote:

Hey Guys,

What I would like to do is break down the characters in a cell and analyze
them. The ultimate use for this is going to be because I want to copy all
the characters before the third space in a cell. I tried several things
using things similar to:

Cells(a,b).Characters(Start:=i, Length:=1).Select
character_check = Selection
If character_check = " " then
space_count = space_count + 1

and so on...

The code breaks every time the macro hits the first line in this section.
If i just could figure out how to check each character in a cell, i could do
the rest myself i think. Does anyone have any help on how to look at each
character of the cell and check what it is? Thanks for the help guys!

Character Analysis
 

I have one more question for you Mr. Skaria,

I am writing what will ultimately be a pretty indepth macro, and I'd like to
utilize what I know in fortran as Functions. I don't know necessarily if
that's what they're called in VB or if it's even possible, but say for
example I wanted to use this code:
lr = Cells(Rows.Count, desc).End(xlUp).Row
With Range(Cells(2, desc), Cells(lr, desc))
Set c = .Find(What:="STD&BTR", LookIn:=xlValues, _
LookAt:=xlPart, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False)
If Not c Is Nothing Then
firstaddress = c.Address
Do
Cells(c.Row, item_num) = Cells(c.Row, item_num)
& "2"
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <
firstaddress
End If
End With

Many times, but didn't want to have that many lines of code. I just wanted
to pass information into this little routine here for what to search for, and
then pass out a value of true or false. How would I go about doing that? If
that needs more detail, let me know, again, I REALLY appreciate all your help!

"Jacob Skaria" wrote:

Replaced X and spaces inbetween

Sub Macro()
Dim strData As String
strData = Trim(Range("A1")) & Space(3)
strData = Trim(Replace(strData, Split(strData, " ", 4)(3), ""))
strData = Replace(Replace(strData, "X", ""), " ", "")
Range("B1") = strData
End Sub


If this post helps click Yes
---------------
Jacob Skaria


"Derek Johansen" wrote:

Hey Guys,

What I would like to do is break down the characters in a cell and analyze
them. The ultimate use for this is going to be because I want to copy all
the characters before the third space in a cell. I tried several things
using things similar to:

Cells(a,b).Characters(Start:=i, Length:=1).Select
character_check = Selection
If character_check = " " then
space_count = space_count + 1

and so on...

The code breaks every time the macro hits the first line in this section.
If i just could figure out how to check each character in a cell, i could do
the rest myself i think. Does anyone have any help on how to look at each
character of the cell and check what it is? Thanks for the help guys!

Character Analysis
 

Thanks Derek for your feedback; and welcome to this discussion group...

I have modified the code to suit your requirement. YES it is called Function
in VBA as in all other programming languages. The below function returns a
boolean value if a search is found

Function FindinRange(rngRange As Range, strFind) As Boolean
Dim rngTemp As Range
Set rngTemp = rngRange.Find(strFind, LookIn:=xlValues, LookAt:=xlPart, _
SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False)
If Not rngTemp Is Nothing Then FindinRange = True
End Function

To use this in macro

Sub Macro1()
Dim rngMyRange As Range
Set rngMyRange = Range("A1:B100")

If FindinRange(rngMyRange, "derek") = True Then
MsgBox "Found"
'do something
End If

End Sub

OR--------------------------------------------------------------------

You can use the worksheet function itself (a little bit slower)

Sub Macro2 ()
IF Worksheetfunction.CountIf(Range("A1:B100"),"derek" )0 Then
Msgbox "Found"
'do something
End If
End Sub


If this post helps click Yes
---------------
Jacob Skaria


"Derek Johansen" wrote:

I have one more question for you Mr. Skaria,

I am writing what will ultimately be a pretty indepth macro, and I'd like to
utilize what I know in fortran as Functions. I don't know necessarily if
that's what they're called in VB or if it's even possible, but say for
example I wanted to use this code:
lr = Cells(Rows.Count, desc).End(xlUp).Row
With Range(Cells(2, desc), Cells(lr, desc))
Set c = .Find(What:="STD&BTR", LookIn:=xlValues, _
LookAt:=xlPart, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False)
If Not c Is Nothing Then
firstaddress = c.Address
Do
Cells(c.Row, item_num) = Cells(c.Row, item_num)
& "2"
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <
firstaddress
End If
End With

Many times, but didn't want to have that many lines of code. I just wanted
to pass information into this little routine here for what to search for, and
then pass out a value of true or false. How would I go about doing that? If
that needs more detail, let me know, again, I REALLY appreciate all your help!

"Jacob Skaria" wrote:

Replaced X and spaces inbetween

Sub Macro()
Dim strData As String
strData = Trim(Range("A1")) & Space(3)
strData = Trim(Replace(strData, Split(strData, " ", 4)(3), ""))
strData = Replace(Replace(strData, "X", ""), " ", "")
Range("B1") = strData
End Sub


If this post helps click Yes
---------------
Jacob Skaria


"Derek Johansen" wrote:

Hey Guys,

What I would like to do is break down the characters in a cell and analyze
them. The ultimate use for this is going to be because I want to copy all
the characters before the third space in a cell. I tried several things
using things similar to:

Cells(a,b).Characters(Start:=i, Length:=1).Select
character_check = Selection
If character_check = " " then
space_count = space_count + 1

and so on...

The code breaks every time the macro hits the first line in this section.
If i just could figure out how to check each character in a cell, i could do
the rest myself i think. Does anyone have any help on how to look at each
character of the cell and check what it is? Thanks for the help guys!

Character Analysis
 

Thank you very much! I appreciate it and so you know, i plan to try to find
you for questions in the future!

Thanks,

Derek


"Jacob Skaria" wrote:

Thanks Derek for your feedback; and welcome to this discussion group...

I have modified the code to suit your requirement. YES it is called Function
in VBA as in all other programming languages. The below function returns a
boolean value if a search is found

Function FindinRange(rngRange As Range, strFind) As Boolean
Dim rngTemp As Range
Set rngTemp = rngRange.Find(strFind, LookIn:=xlValues, LookAt:=xlPart, _
SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False)
If Not rngTemp Is Nothing Then FindinRange = True
End Function

To use this in macro

Sub Macro1()
Dim rngMyRange As Range
Set rngMyRange = Range("A1:B100")

If FindinRange(rngMyRange, "derek") = True Then
MsgBox "Found"
'do something
End If

End Sub

OR--------------------------------------------------------------------

You can use the worksheet function itself (a little bit slower)

Sub Macro2 ()
IF Worksheetfunction.CountIf(Range("A1:B100"),"derek" )0 Then
Msgbox "Found"
'do something
End If
End Sub


If this post helps click Yes
---------------
Jacob Skaria


"Derek Johansen" wrote:

I have one more question for you Mr. Skaria,

I am writing what will ultimately be a pretty indepth macro, and I'd like to
utilize what I know in fortran as Functions. I don't know necessarily if
that's what they're called in VB or if it's even possible, but say for
example I wanted to use this code:
lr = Cells(Rows.Count, desc).End(xlUp).Row
With Range(Cells(2, desc), Cells(lr, desc))
Set c = .Find(What:="STD&BTR", LookIn:=xlValues, _
LookAt:=xlPart, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False)
If Not c Is Nothing Then
firstaddress = c.Address
Do
Cells(c.Row, item_num) = Cells(c.Row, item_num)
& "2"
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <
firstaddress
End If
End With

Many times, but didn't want to have that many lines of code. I just wanted
to pass information into this little routine here for what to search for, and
then pass out a value of true or false. How would I go about doing that? If
that needs more detail, let me know, again, I REALLY appreciate all your help!

"Jacob Skaria" wrote:

Replaced X and spaces inbetween

Sub Macro()
Dim strData As String
strData = Trim(Range("A1")) & Space(3)
strData = Trim(Replace(strData, Split(strData, " ", 4)(3), ""))
strData = Replace(Replace(strData, "X", ""), " ", "")
Range("B1") = strData
End Sub


If this post helps click Yes
---------------
Jacob Skaria


"Derek Johansen" wrote:

Hey Guys,

What I would like to do is break down the characters in a cell and analyze
them. The ultimate use for this is going to be because I want to copy all
the characters before the third space in a cell. I tried several things
using things similar to:

Cells(a,b).Characters(Start:=i, Length:=1).Select
character_check = Selection
If character_check = " " then
space_count = space_count + 1

and so on...

The code breaks every time the macro hits the first line in this section.
If i just could figure out how to check each character in a cell, i could do
the rest myself i think. Does anyone have any help on how to look at each
character of the cell and check what it is? Thanks for the help guys!