NEWBIE: Looking for Function to Subract a letter?

[lbbs]

E.G. S.CBA

looking for a function or macro that will subtract "S." and give me a result
of
CBA. THANKS.

 

[Tom Ogilvy]

=Right("S.CBA",3)

 

[JulieD]

Hi

assuming all your data is exactly like the example you can use in cell B1
(for example)
=RIGHT(A1,3)
(assumes data is in A1 - adjust as necessary)
this returns 3 characters from the right
and copy down as many rows as needed

if, however, the length of the string can be longer but always starts with
"S." - which you want to get rid off, you could use this formula:
=RIGHT(A1,LEN(A1)-2)

Let us know how you go

Cheers
JulieD

 

[Frank Kabel]

Hi
try
=SUBSTITUTE(A1,"S.","")
if A1 stores your string value

 

[Bob Phillips]

=SUBSTITUTE(A1,"S.","")

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[lbbs]

To clarify my data is not always in A1 and is not always the same length or
letter.
this is a typical data list, and I always want to get rid of the first 2
characters.

T.CAA
G.CAG
S.VAD
B.DCC

etc...
I want everything after the period.
It would take too long to manually delete the first letter of every cell.

 

[JulieD]

Hi

did you try my solution?

Cheers
JulieD

To clarify my data is not always in A1 and is not always the same length or
letter.
this is a typical data list, and I always want to get rid of the first 2
characters.

T.CAA
G.CAG
S.VAD
B.DCC

etc...
I want everything after the period.
It would take too long to manually delete the first letter of every cell.

 

[Frank Kabel]

Hi
put the following formula in the adjacent column 8assumption your data
is in column A)
=MID(A1,FIND(".",A1)+1,1024)
copy this down

After this you may copy this helper column and insert it again as
'Values' to clear the formulas (goto 'Edit - Paste Special' to achieve
this)

 

[Bob Phillips]

It would help if you stated your requirement rather than one example of the
data

=MID(A1,FIND(".",A1)+1,99)

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Tom Ogilvy]

Sub ClearFirstTwo()
Dim rng as Range, cell as Range
set rng = Range(cells(1,1),Cells(1,1).End(xldown))
for each cell in rng
cell.value = Right(cell.value,len(cell.value)-2)
Next
End Sub

This will make the changes in place.

Test it on a copy of your data.

If the spec is any letters to the right of the period

Sub ClearToPeriod()
Dim rng as Range, cell as Range
Dim iloc as Long
set rng = Range(cells(1,1),Cells(1,1).End(xldown))
for each cell in rng
iloc = Instr(1,cell,".",vbTextCompare)
if iloc <> 0 then
cell.value = Right(cell.value,len(cell.value)-iloc)
end sub
Next
End Sub