From: andreashermle on
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
From: Helmut Meukel on
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" <andreas.hermle(a)gmx.de> schrieb im Newsbeitrag
news:d1e01d48-2a3a-4904-a0ad-9af4dcc26820(a)q23g2000vba.googlegroups.com...
> 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


From: andreashermle on
On 31 Mai, 11:20, "Helmut Meukel" <NoS...(a)NoProvider.de> 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" <andreas.her...(a)gmx.de> schrieb im Newsbeitragnews:d1e01d48-2a3a-4904-a0ad-9af4dcc26820(a)q23g2000vba.googlegroups.com...
>
>
>
> > 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
From: Helmut Meukel on
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" <andreas.hermle(a)gmx.de> schrieb im Newsbeitrag
news:27059101-3455-4926-8904-83e48c7e3da9(a)c11g2000vbe.googlegroups.com...
> On 31 Mai, 11:20, "Helmut Meukel" <NoS...(a)NoProvider.de> 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" <andreas.her...(a)gmx.de> schrieb im
> > Newsbeitragnews:d1e01d48-2a3a-4904-a0ad-9af4dcc26820(a)q23g2000vba.googlegroups.com...
> >
> > > 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

>
From: andreashermle on
On 31 Mai, 13:46, "Helmut Meukel" <NoS...(a)NoProvider.de> 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" <andreas.her...(a)gmx.de> schrieb im Newsbeitragnews:27059101-3455-4926-8904-83e48c7e3da9(a)c11g2000vbe.googlegroups.com...
>
>
>
> > On 31 Mai, 11:20, "Helmut Meukel" <NoS...(a)NoProvider.de> 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" <andreas.her...(a)gmx.de> schrieb im
> > > Newsbeitragnews:d1e01d48-2a3a-4904-a0ad-9af4dcc26820(a)q23g2000vba.googlegroups.com...
>
> > > > 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
 |  Next  |  Last
Pages: 1 2
Prev: Change Times
Next: Problem with - sign in sheet name??