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 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

Sorry,

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

Helmut.

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