Hi,

Could anyone help how to use Macro to move and modify character in cell for
the below situation:

Case 1) String "7 units here" at cell A1 on column A. Modify to adding some
spaces at the cell A1. As the result, the content of A1 become " 7
units here.

Case 2) String "7 units here" at cell A1 in column A. String "There are" at
Cell B2 in columnB. How to move and combine them into "There are 7 units
here" store at Cell C1 in columnC.

Thanks for in advance.

Tlee

sub test
Range("C1").Value = Range("B1") & Space(1) & Range("A1")
end sub


u can use function also in Cell C1 put this formula
= B1 & " " & A1




On Jun 7, 3:06*pm, "tlee" wrote:
Hi,

Could anyone help how to use Macro to move and modify character in cell for
the below situation:

Case 1) String "7 units here" at cell A1 on column A. *Modify to adding some
spaces at the cell A1. As the result, the content of A1 become " * * * * 7
units here.

Case 2) *String "7 units here" at cell A1 in column A. String "There are" at
Cell B2 in columnB. How to move and combine them into "There are 7 units
here" store at Cell C1 in columnC.

Thanks for in advance.

Tlee

Function xtractAftrDelim(sValue As String, Optional sSeparator = " ") As
String
' extracts the end of the string after the first separator
' if no other separator is specified, default separator is a blank space

Dim iPos
iPos = InStr(sValue, sSeparator)

xtractAftrDelim = Right(sValue, Len(sValue) - iPos)

End Function


Sub test()

Range("A1") = xtractAftrDelim(Range("A1"))
End Sub


Sub CombineCells(rng)
'combine values of cell with one cell to the right in 2nd cell to right

Range(rng).Offset(, 2) = Range(rng).Value & " " & Range(rng).Offset(,
1).Value
End Sub

Sub test2()
CombineCells ("A1")
End Sub

--
Steve

"tlee" wrote in message
...
Hi,

Could anyone help how to use Macro to move and modify character in cell
for the below situation:

Case 1) String "7 units here" at cell A1 on column A. Modify to adding
some spaces at the cell A1. As the result, the content of A1 become "
7 units here.

Case 2) String "7 units here" at cell A1 in column A. String "There are"
at Cell B2 in columnB. How to move and combine them into "There are 7
units here" store at Cell C1 in columnC.

Thanks for in advance.

Tlee

Thanks all,

if I want to loop all cells in columns, how can I code it?

Thanks for in advance.

Tlee


Function xtractAftrDelim(sValue As String, Optional sSeparator = " ") As
String
' extracts the end of the string after the first separator
' if no other separator is specified, default separator is a blank space

Dim iPos
iPos = InStr(sValue, sSeparator)

xtractAftrDelim = Right(sValue, Len(sValue) - iPos)

End Function


Sub test()

Range("A1") = xtractAftrDelim(Range("A1"))
End Sub


Sub CombineCells(rng)
'combine values of cell with one cell to the right in 2nd cell to right

Range(rng).Offset(, 2) = Range(rng).Value & " " & Range(rng).Offset(,
1).Value
End Sub

Sub test2()
CombineCells ("A1")
End Sub

--
Steve

"tlee" wrote in message
...
Hi,

Could anyone help how to use Macro to move and modify character in cell
for the below situation:

Case 1) String "7 units here" at cell A1 on column A. Modify to adding
some spaces at the cell A1. As the result, the content of A1 become " 7
units here.

Case 2) String "7 units here" at cell A1 in column A. String "There are"
at Cell B2 in columnB. How to move and combine them into "There are 7
units here" store at Cell C1 in columnC.

Thanks for in advance.

Tlee

Hi Steve,

Thanks for your help !!!

tlee


Part 1 - modify the strings in column A.

SelectColAValues selects EVERYTHING in column A
ProcessChange pulls all values into and array and runs the function to
strip the first 'word'
Note : xtractAftrDelim modified to pass the string from the array ByVal

Sub runit()
Call SelectColAValues
Call ProcessChange
End Sub

Private Function xtractAftrDelim(ByVal sValue As String, Optional
sSeparator = " ") As String
' extracts the end of the string after the first separator
' if no other separator is specified, default separator is a blank space

Dim iPos
iPos = InStr(sValue, sSeparator)

xtractAftrDelim = Right(sValue, Len(sValue) - iPos)

End Function

Private Sub SelectColAValues()

Application.Goto Reference:="R65536C1"
Selection.End(xlUp).Select
Range(Selection, Selection.End(xlUp)).Select
End Sub

Private Sub ProcessChange()
Dim buf As Variant
Dim i As Long

'Get values as an array
buf = Selection

'loop through the array and modify each element
For i = LBound(buf, 1) To UBound(buf, 1)
buf(i, 1) = xtractAftrDelim(buf(i, 1))
Next

'Put the array back into the worksheet
Selection = buf

End Sub


Part 2 add a formula to column C to combine column A and B values

Sub AddFormulaToColC()

SelectColAValues' use the same sub as above to select
Selection.Offset(, 2).FormulaR1C1 = "=RC[-2]& "" "" & RC[-1]"

End Sub



--
Steve

"tlee" wrote in message
...
Thanks all,

if I want to loop all cells in columns, how can I code it?

Thanks for in advance.

Tlee


Function xtractAftrDelim(sValue As String, Optional sSeparator = " ") As
String
' extracts the end of the string after the first separator
' if no other separator is specified, default separator is a blank
space

Dim iPos
iPos = InStr(sValue, sSeparator)

xtractAftrDelim = Right(sValue, Len(sValue) - iPos)

End Function


Sub test()

Range("A1") = xtractAftrDelim(Range("A1"))
End Sub


Sub CombineCells(rng)
'combine values of cell with one cell to the right in 2nd cell to right

Range(rng).Offset(, 2) = Range(rng).Value & " " & Range(rng).Offset(,
1).Value
End Sub

Sub test2()
CombineCells ("A1")
End Sub

--
Steve

"tlee" wrote in message
...
Hi,

Could anyone help how to use Macro to move and modify character in cell
for the below situation:

Case 1) String "7 units here" at cell A1 on column A. Modify to adding
some spaces at the cell A1. As the result, the content of A1 become " 7
units here.

Case 2) String "7 units here" at cell A1 in column A. String "There
are" at Cell B2 in columnB. How to move and combine them into "There
are 7 units here" store at Cell C1 in columnC.

Thanks for in advance.

Tlee