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
9
193 Views
Last Modified: 2012-05-11
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
Comment
Question by:dmalovich
  • 3
  • 3
  • 2
  • +1
9 Comments
 
LVL 10

Expert Comment

by:cdebel
Comment Utility
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

by:cdebel
Comment Utility
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

by:Rory Archibald
Comment Utility
(NFP)
The test will not be case sensitive, so there is no need for UPPER here, FYI.
0
 
LVL 10

Expert Comment

by:cdebel
Comment Utility
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
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 

Author Comment

by:dmalovich
Comment Utility
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

by:barry houdini
Comment Utility
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

by:
Rory Archibald earned 500 total points
Comment Utility
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  

Open in new window


for example.
0
 

Author Closing Comment

by:dmalovich
Comment Utility
Awesome.  Thanks....
0
 
LVL 85

Expert Comment

by:Rory Archibald
Comment Utility
Since you didn't specify VBA in the original question, I think a points split would be more appropriate here?
0

Featured Post

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.” David Miller (dlmille)  It was one of those days… I wa…
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now