Remove Leading Spaces

[Kirk P.]

I've got a column of cells filled with Product ID's. They all have a space
at the beginning, such as

' 9025342
' 1023456

I've tried Trim, LTrim, Clean, etc, etc. Nothing seems to work on these
cells. Any ideas?

[Michael Malinsky]

Assuming all entries have the apostrophe-single space combo at the beginning
of the product number, try this:

=RIGHT(A1,LEN(A1)-2)

"Kirk P." <[email protected]> wrote in message
news:[email protected]...
> I've got a column of cells filled with Product ID's. They all have a
space
> at the beginning, such as
>
> ' 9025342
> ' 1023456
>
> I've tried Trim, LTrim, Clean, etc, etc. Nothing seems to work on these
> cells. Any ideas?

[Jim May]

In a standard module paste in:

Sub nopreapostophe()
For Each c In Selection
If c.PrefixCharacter = "'" Then c.Value = c.Value
Next
End Sub

Back at the worksheet, Highlight/Select your desired range of Cells to
examine/fix.
And then run: nopreapostophe()
HTH


"Michael Malinsky" <[email protected]> wrote in message
news:[email protected]...
> Assuming all entries have the apostrophe-single space combo at the
beginning
> of the product number, try this:
>
> =RIGHT(A1,LEN(A1)-2)
>
> "Kirk P." <[email protected]> wrote in message
> news:[email protected]...
> > I've got a column of cells filled with Product ID's. They all have a
> space
> > at the beginning, such as
> >
> > ' 9025342
> > ' 1023456
> >
> > I've tried Trim, LTrim, Clean, etc, etc. Nothing seems to work on these
> > cells. Any ideas?
>
>

[Jim May]

Sorry, I got your Q confused with another Q asked by someone - their Q,
which had to do
only with the removal of the " ' " preceeding a bunch of numbers..


"Jim May" <[email protected]> wrote in message
news:4ZDVd.30376$7z6.29057@lakeread04...
> In a standard module paste in:
>
> Sub nopreapostophe()
> For Each c In Selection
> If c.PrefixCharacter = "'" Then c.Value = c.Value
> Next
> End Sub
>
> Back at the worksheet, Highlight/Select your desired range of Cells to
> examine/fix.
> And then run: nopreapostophe()
> HTH
>
>
> "Michael Malinsky" <[email protected]> wrote in message
> news:[email protected]...
> > Assuming all entries have the apostrophe-single space combo at the
> beginning
> > of the product number, try this:
> >
> > =RIGHT(A1,LEN(A1)-2)
> >
> > "Kirk P." <[email protected]> wrote in message
> > news:[email protected]...
> > > I've got a column of cells filled with Product ID's. They all have a
> > space
> > > at the beginning, such as
> > >
> > > ' 9025342
> > > ' 1023456
> > >
> > > I've tried Trim, LTrim, Clean, etc, etc. Nothing seems to work on
these
> > > cells. Any ideas?
> >
> >
>
>

[cycling-rod]

Try putting a space in Find (click in the Find slot and tap space bar once), leave Replace blank, and choose Replace all and OK.

[steallan]

Hi
ASAP utilities excel add on, there is a delete leading and trailing spaces function. If you haven't got this add on get it it will save you hours.

type ASAP utilities in google

[mostakimm]

hi

how about, if there is a column of dates that have trailing * , but some are 8-may-05* and some are 02-nov-99* , need to remove that trailing * .

find and replace dose not accept it, it concider it as a wild selection.

if i insert a space before the * , then use the find and replace , it would work.

any advice.
thank you