Replacing spaces with underscore for specific expressions
in [Programming]
|
Prev: Change Times
Next: Problem with - sign in sheet name??
From: andreashermle on 31 May 2010 03:36 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 31 May 2010 05:20 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 31 May 2010 07:00 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 31 May 2010 07:46 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 31 May 2010 11:48
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 |