Replacing a last digit to something else

[pjamorton]

Hi all,

I hope you can help me. I have a huge excel spreadsheet and all I want
to do is replace the last digit in each cell, no matter what it is,
number or letter, into an L or an M. One worksheet will be "L"'s and
one will be "M"'s. My biggest concern is just changing the last digit.
Any ideas? Thank you very much in advance! Jen

 

[Don Guillett]

Sub replacelastchrwithM()
For Each c In Selection
c.Value = Left(c, Len(c) - 1) & "M"
Next c
End Sub

 

[Nick Hodge]

Jen

Use a helper column alongside the current one and a formula (Presume A)

=LEFT(A1,LEN(A1)-1)&"L"

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
(e-mail address removed)
www.nickhodge.co.uk

 

[pjamorton]

Thank you! That worked wonderfully. Now, my next question is how to
change only the third digit in a cell to an "L". The number would look
something like 150-123-456. I would want to change the 0 into an "L".
How would I do that? Thanks in advance! Jen

 

[David Biddulph]

=LEFT(A1,2)&"L"&RIGHT(A1,LEN(A1)-3)

 

[Don Guillett]

To do with a formula look in the help index for MID

 

[pjamorton]

Thank you SOOOO much!! That worked like a charm! I am on Cloud 9! This
makes my work so much easier and faster!! You guys are awesome! I
cannot thank you enough! Jen