Dear Experts:

I got numbers in column C with the following Synthax (xxxxSpacexxx),
e.g.

0250 434 or
0748 314

All these expressions are located in Column C and the 'Space' should
be replaced with an 'Underscore'.
After the replacement the expressions should look like this: 0250_434
or 0748_314

Please note: There are other expressions in cells of column C, such as
192344 / 134374. But those spaces should not be replaced with the
underscore character.

I would like to run a macro for this problem.

Thank you very much in advance for your professional help.

Regards, Andreas

Andreas,

insufficient data.
To find an optimal solution you should provide more data.
- is the space *always* in the fifth position? If it is,
should it always be replaced?
- should a space surrounded by numbers *always* be replaced?
Please define your criteria better.

Helmut.


"andreashermle" schrieb im Newsbeitrag
...
Dear Experts:

I got numbers in column C with the following Synthax (xxxxSpacexxx),
e.g.

0250 434 or
0748 314

All these expressions are located in Column C and the 'Space' should
be replaced with an 'Underscore'.
After the replacement the expressions should look like this: 0250_434
or 0748_314

Please note: There are other expressions in cells of column C, such as
192344 / 134374. But those spaces should not be replaced with the
underscore character.

I would like to run a macro for this problem.

Thank you very much in advance for your professional help.

Regards, Andreas

On 31 Mai, 11:20, "Helmut Meukel" wrote:
Andreas,

insufficient data.
To find an optimal solution you should provide more data.
- is the space *always* in the fifth position? If it is,
* *should it always be replaced?
- should a space surrounded by numbers *always* be replaced?
Please define your criteria better.

Helmut.

"andreashermle" schrieb im ...



Dear Experts:

I got numbers in column C with the following Synthax (xxxxSpacexxx),
e.g.

0250 434 or
0748 314

All these expressions are located in Column C and the 'Space' should
be replaced with an 'Underscore'.
After the replacement the expressions should look like this: 0250_434
or 0748_314

Please note: There are other expressions in cells of column C, such as
192344 / 134374. But those spaces should not be replaced with the
underscore character.

I would like to run a macro for this problem.

Thank you very much in advance for your professional help.

Regards, Andreas- Zitierten Text ausblenden -

- Zitierten Text anzeigen -

Hi Helmut

thank you very much for your swift response.

ok, you are right on second thoughts.

-The space could occurr on the 4th or 6h position of this expression
as well
- If the string/expression in a cell contains a backslash such as
'192344 / 134374', the cell is to be skipped

Regards, Andreas

Andreas,
assuming you meant a slash, not a backslash (\):

If Instr(1, CellString, "/") = 0 then
For i = 4 to 6 'no need to test for other positions (?)
If Mid(CellString, i, 1) = " " then Mid(CellString, i, 1) = "_"
Next i
' in case there were double or triple blanks we now should
' remove the excess underscores
do while Instr(4, CellString, "__")
Replace(CellString, "__", "_")
loop
endif

Helmut.

"andreashermle" schrieb im Newsbeitrag
...
On 31 Mai, 11:20, "Helmut Meukel" wrote:
Andreas,

insufficient data.
To find an optimal solution you should provide more data.
- is the space *always* in the fifth position? If it is,
should it always be replaced?
- should a space surrounded by numbers *always* be replaced?
Please define your criteria better.

Helmut.

"andreashermle" schrieb im
...

Dear Experts:

I got numbers in column C with the following Synthax (xxxxSpacexxx),
e.g.
0250 434 or
0748 314

All these expressions are located in Column C and the 'Space' should
be replaced with an 'Underscore'.
After the replacement the expressions should look like this: 0250_434
or 0748_314

Please note: There are other expressions in cells of column C, such as
192344 / 134374. But those spaces should not be replaced with the
underscore character.

I would like to run a macro for this problem.

Hi Helmut

thank you very much for your swift response.

ok, you are right on second thoughts.

-The space could occurr on the 4th or 6h position of this expression
as well
- If the string/expression in a cell contains a backslash such as
'192344 / 134374', the cell is to be skipped

Regards, Andreas

On Mon, 31 May 2010 00:36:15 -0700 (PDT), andreashermle
wrote:

Dear Experts:

I got numbers in column C with the following Synthax (xxxxSpacexxx),
e.g.

0250 434 or
0748 314

All these expressions are located in Column C and the 'Space' should
be replaced with an 'Underscore'.
After the replacement the expressions should look like this: 0250_434
or 0748_314

Please note: There are other expressions in cells of column C, such as
192344 / 134374. But those spaces should not be replaced with the
underscore character.

I would like to run a macro for this problem.

Thank you very much in advance for your professional help.

Regards, Andreas

I am assuming from your examples that "xxx" has to be digits, and also that
there is nothing else in the cell. If that is not the case, the patterns below
can be changed.

=============================
Option Explicit
Sub InsertUnderscore()
Dim rg As Range, c As Range
Dim s As Variant
Set rg = Range("C1")

'find first cell in column C
If Len(rg.Text) = 0 Then Set rg = rg.End(xlDown)
'find last cell in column c and set range
Set rg = Range(rg, rg(Cells.Rows.Count - rg.Row, 1).End(xlUp))

'cycle through range and insert underscore
For Each c In rg
s = c.Text
If s Like "*# #*" Then
s = Split(s, " ")
If IsNumeric(s(0)) And IsNumeric(s(1)) _
And UBound(s) = 1 Then
c.Value = Join(s, "_")
End If
End If
Next c
End Sub
===========================

This can also be done using Regular Expressions. The advantage is that the
description of the pattern is simpler and can be easily modified; the
disadvantage is that it will probably run a bit slower.

=================================
Option Explicit
Sub InsertUnderscoreRE()
Dim rg As Range, c As Range
Dim re As Object
Set rg = Range("C1")

'find first cell in column C
If Len(rg.Text) = 0 Then Set rg = rg.End(xlDown)
'find last cell in column c and set range
Set rg = Range(rg, rg(Cells.Rows.Count - rg.Row, 1).End(xlUp))

'set regex
Set re = CreateObject("vbscript.regexp")
re.Global = True
re.Pattern = "^(\d+)\s(\d+)$"
'cycle through range and insert underscore
For Each c In rg
c.Value = re.Replace(c.Value, "$1_$2")
Next c
End Sub
=============================
--ron

On 31 Mai, 13:46, "Helmut Meukel" wrote:
Andreas,
assuming you meant a slash, not a backslash (\):

* * If Instr(1, CellString, "/") = 0 then
* * * * For i = 4 to 6 * *'no need to test for other positions (?)
* * * * * * If Mid(CellString, i, 1) = " " then Mid(CellString, i, 1) = "_"
* * * * Next i
* * * * ' in case there were double or triple blanks we now should
* * * * ' remove the excess underscores
* * * * do while Instr(4, CellString, "__")
* * * * * * Replace(CellString, "__", "_")
* * * * loop
* * endif

Helmut.

"andreashermle" schrieb im ...



On 31 Mai, 11:20, "Helmut Meukel" wrote:
Andreas,

insufficient data.
To find an optimal solution you should provide more data.
- is the space *always* in the fifth position? If it is,
should it always be replaced?
- should a space surrounded by numbers *always* be replaced?
Please define your criteria better.

Helmut.

"andreashermle" schrieb im
...

Dear Experts:

I got numbers in column C with the following Synthax (xxxxSpacexxx),
e.g.
0250 434 or
0748 314

All these expressions are located in Column C and the 'Space' should
be replaced with an 'Underscore'.
After the replacement the expressions should look like this: 0250_434
or 0748_314

Please note: There are other expressions in cells of column C, such as
192344 / 134374. But those spaces should not be replaced with the
underscore character.

I would like to run a macro for this problem.

Hi Helmut

thank you very much for your swift response.

ok, you are right on second thoughts.

-The space could occurr on the 4th or 6h position of this expression
as well
- If the string/expression in a cell contains a backslash such as
'192344 / 134374', the cell is to be skipped

Regards, Andreas- Zitierten Text ausblenden -

- Zitierten Text anzeigen -

Hi Helmut,

thank you very much for your great help. I am afraid to tell that your
code throws an error message on line 'Replace(CellString, "__", "_")'

Regards, Andreas

On 31 Mai, 14:01, Ron Rosenfeld wrote:
On Mon, 31 May 2010 00:36:15 -0700 (PDT), andreashermle
wrote:





Dear Experts:

I got numbers in column C with the following Synthax (xxxxSpacexxx),
e.g.

0250 434 or
0748 314

All these expressions are located in Column C and the 'Space' should
be replaced with an 'Underscore'.
After the replacement the expressions should look like this: 0250_434
or 0748_314

Please note: There are other expressions in cells of column C, such as
192344 / 134374. But those spaces should not be replaced with the
underscore character.

I would like to run a macro for this problem.

Thank you very much in advance for your professional help.

Regards, Andreas

I am assuming from your examples that "xxx" has to be digits, and also that
there is nothing else in the cell. *If that is not the case, the patterns below
can be changed.

=============================
Option Explicit
Sub InsertUnderscore()
* * Dim rg As Range, c As Range
* * Dim s As Variant
* * Set rg = Range("C1")

'find first cell in column C
If Len(rg.Text) = 0 Then Set rg = rg.End(xlDown)
'find last cell in column c and set range
Set rg = Range(rg, rg(Cells.Rows.Count - rg.Row, 1).End(xlUp))

'cycle through range and insert underscore
* * For Each c In rg
* * * * s = c.Text
* * * * If s Like "*# #*" Then
* * * * * * s = Split(s, " ")
* * * * * * If IsNumeric(s(0)) And IsNumeric(s(1)) _
* * * * * * * * * * And UBound(s) = 1 Then
* * * * * * * * c.Value = Join(s, "_")
* * * * * * End If
* * * * End If
* * Next c
End Sub
===========================

This can also be done using Regular Expressions. *The advantage is that the
description of the pattern is simpler and can be easily modified; the
disadvantage is that it will probably run a bit slower.

=================================
Option Explicit
Sub InsertUnderscoreRE()
*Dim rg As Range, c As Range
*Dim re As Object
* * Set rg = Range("C1")

'find first cell in column C
If Len(rg.Text) = 0 Then Set rg = rg.End(xlDown)
'find last cell in column c and set range
Set rg = Range(rg, rg(Cells.Rows.Count - rg.Row, 1).End(xlUp))

'set regex
* * Set re = CreateObject("vbscript.regexp")
* * * * re.Global = True
* * * * re.Pattern = "^(\d+)\s(\d+)$"
'cycle through range and insert underscore
* * For Each c In rg
* * * * c.Value = re.Replace(c.Value, "$1_$2")
* * Next c
End Sub
=============================
--ron- Zitierten Text ausblenden -

- Zitierten Text anzeigen -

Hi Ron,

as always from your side. Nice coding that works just fine. I really
appreciate your superb support. Thank you very much.

Regards, Andreas

Sorry,

Replace(CellString, "__", "_")
should have been
CellString = Replace(CellString, "__", "_")

Helmut.

On Mon, 31 May 2010 08:48:55 -0700 (PDT), andreashermle
wrote:

Hi Ron,

as always from your side. Nice coding that works just fine. I really
appreciate your superb support. Thank you very much.

Regards, Andreas

You're welcome. Glad to help. Thanks for the feedback.

--Ron

On 31 Mai, 18:16, "Helmut Meukel" wrote:
Sorry,

Replace(CellString, "__", "_")

should have been
* * CellString = Replace(CellString, "__", "_")

Helmut.

Hi Helmut,

great, that did the trick. Thank you very much for your professional
help.
Regards, Andreas