I would like to insert a , with a cell.
i have Wood T 3,1
i would like to have
Wood T,3/1
I would like to insert a , with a cell.
i have Wood T 3,1
i would like to have
Wood T,3/1
Sorry it should read
I have Woods T 3/1
i want Woods T, 3/1
Thank you
"Hayley" wrote:
> I would like to insert a , with a cell.
>
> i have Wood T 3,1
>
> i would like to have
> Wood T,3/1
If cell A1 contains (surname)(single space)(single initial)(single
space)(score), the following will achieve what you want:
=LEFT(A1,FIND(" ",A1)+1)&","&RIGHT(A1,LEN(A1)-LEN(LEFT(A1,FIND(" ",A1)+1)))
HTH
"Hayley" wrote:
> Sorry it should read
> I have Woods T 3/1
> i want Woods T, 3/1
>
> Thank you
>
> "Hayley" wrote:
>
> > I would like to insert a , with a cell.
> >
> > i have Wood T 3,1
> >
> > i would like to have
> > Wood T,3/1
How about:
=REPLACE(A1,SEARCH(" ",A1,1)+2,2,", ")
It looks for the first space character, comes over two more, and replaces the
space with a space-comma.
Hayley wrote:
>
> Sorry it should read
> I have Woods T 3/1
> i want Woods T, 3/1
>
> Thank you
>
> "Hayley" wrote:
>
> > I would like to insert a , with a cell.
> >
> > i have Wood T 3,1
> >
> > i would like to have
> > Wood T,3/1
--
Dave Peterson
Thanks Dave - it works been bugging me for ages. thanks again
"Dave Peterson" wrote:
> How about:
>
> =REPLACE(A1,SEARCH(" ",A1,1)+2,2,", ")
>
> It looks for the first space character, comes over two more, and replaces the
> space with a space-comma.
>
>
>
> Hayley wrote:
> >
> > Sorry it should read
> > I have Woods T 3/1
> > i want Woods T, 3/1
> >
> > Thank you
> >
> > "Hayley" wrote:
> >
> > > I would like to insert a , with a cell.
> > >
> > > i have Wood T 3,1
> > >
> > > i would like to have
> > > Wood T,3/1
>
> --
>
> Dave Peterson
>
Do you know how I would go about adding a ' at the beginning of each cell in
a column without changing the information already in each cell?
"Dave Peterson" wrote:
> How about:
>
> =REPLACE(A1,SEARCH(" ",A1,1)+2,2,", ")
>
> It looks for the first space character, comes over two more, and replaces the
> space with a space-comma.
>
>
>
> Hayley wrote:
> >
> > Sorry it should read
> > I have Woods T 3/1
> > i want Woods T, 3/1
> >
> > Thank you
> >
> > "Hayley" wrote:
> >
> > > I would like to insert a , with a cell.
> > >
> > > i have Wood T 3,1
> > >
> > > i would like to have
> > > Wood T,3/1
>
> --
>
> Dave Peterson
>
Is a macro ok?
Option Explicit
Sub testme()
Dim myRng As Range
Dim myCell As Range
Set myRng = Nothing
On Error Resume Next
Set myRng = Intersect(Selection, _
Selection.Cells.SpecialCells(xlCellTypeConstants))
On Error GoTo 0
If myRng Is Nothing Then
MsgBox "No constants in selection!"
Exit Sub
End If
For Each myCell In myRng.Cells
If myCell.PrefixCharacter <> "'" Then
myCell.Value = "'" & myCell.Text
End If
Next myCell
End Sub
Select a range and try it out.
If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm
Rachael wrote:
>
> Do you know how I would go about adding a ' at the beginning of each cell in
> a column without changing the information already in each cell?
>
> "Dave Peterson" wrote:
>
> > How about:
> >
> > =REPLACE(A1,SEARCH(" ",A1,1)+2,2,", ")
> >
> > It looks for the first space character, comes over two more, and replaces the
> > space with a space-comma.
> >
> >
> >
> > Hayley wrote:
> > >
> > > Sorry it should read
> > > I have Woods T 3/1
> > > i want Woods T, 3/1
> > >
> > > Thank you
> > >
> > > "Hayley" wrote:
> > >
> > > > I would like to insert a , with a cell.
> > > >
> > > > i have Wood T 3,1
> > > >
> > > > i would like to have
> > > > Wood T,3/1
> >
> > --
> >
> > Dave Peterson
> >
--
Dave Peterson
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks