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