Solved

# I would like to cut the first character of a string if it starts with a T in MS excel

Posted on 2011-03-08
Medium Priority
210 Views
I have a column of cells that have numbers in them.   Some start with a T and some don't.  I would like to cut the T from the cells that start with a T and leave the others alone.
0
Question by:dmalovich
• 3
• 3
• 2
• +1

LVL 10

Expert Comment

ID: 35068779
you could do something like this... if your data is in column A:

=IF(LEFT(A1,1)="T", MID(A1,2,LEN(A1)-1))
0

LVL 10

Expert Comment

ID: 35068793
i forgot the FALSE part of the IF...

Sorry for that, here's the modification:
=IF(LEFT(A1,1)="T", MID(A1,2,LEN(A1)-1), A1)

You could also add UPPER statement to make it work no mather if the T is capital or not, like this:
=IF(UPPER(LEFT(A1,1))="T", MID(A1,2,LEN(A1)-1), A1)
0

LVL 85

Expert Comment

ID: 35068811
(NFP)
The test will not be case sensitive, so there is no need for UPPER here, FYI.
0

LVL 10

Expert Comment

ID: 35068850
true rorya.  Thanks for advising me.  I've never noticed that but Excel doesn't seems to make the difference between "t" and "T" in this case.  I wonder why.

@dmalovich: use the 1st line of my last comment since the upper is not necessary.
=IF(LEFT(A1,1)="T", MID(A1,2,LEN(A1)-1), A1)
0

Author Comment

ID: 35068863
You do have the answer for putting the formula in a cell.  I'm looping through values in vba and would like to chop off the T if its the first character and leave it as is if it is not. example vba

position = Worksheets("something").Cells(1, 1).Value

if position has a T as first character
then chop it off
end if

Is this possible?
0

LVL 50

Expert Comment

ID: 35068892
Here's another option.....

=REPLACE(A1,1,(LEFT(A1)="T"),"")

As Rory says that isn't case-sensitive so it will remove both "T" and "t" if one of those is first letter. If you do want case-sensitivity then this version will only remove T not t

=REPLACE(A1,1,EXACT(LEFT(A1),"T")+0,"")

regards, barry
0

LVL 85

Accepted Solution

Rory Archibald earned 2000 total points
ID: 35068936
``````position = Worksheets("something").Cells(1, 1).Value

if UCase(Left(position, 1)) = "T" then
position = MID\$(position, 2)
Worksheets("something").Cells(1, 1).Value = position
end if
``````

for example.
0

Author Closing Comment

ID: 35068995
Awesome.  Thanks....
0

LVL 85

Expert Comment

ID: 35069007
Since you didn't specify VBA in the original question, I think a points split would be more appropriate here?
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Excel can be a tricky bit of software to get your head around. Whilst youāll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to dā¦
Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
###### Suggested Courses
Course of the Month13 days, 15 hours left to enroll