Trim Formula: Separated By A Colon
Oct 23, 2008
I have been using the following formula's to trim some information that's separated by a colon, such as Pioneer Clinic:Dave McAfee...so that I have a column with Pioneer Clinic and a column with Dave McAfee:
=LEFT(G5,FIND(":",G5)-1)
=TRIM(RIGHT(G5,LEN(G5)-FIND(":",G5)))
However, now some of my data is separated by two colons! AVM:Pioneer Clinic:Dave McAfee. the formula for separating this so I still have two columns of Pioneer Clinic and Dave McAfee?
View 5 Replies
ADVERTISEMENT
Jan 24, 2013
I have a long list of names
Doe, John;Doe Jane; etc..
Is there any way to have Excel separate them into individual cells?
View 2 Replies
View Related
Nov 29, 2012
I found the following formula on another site that deletes all the text before the last colon (it deletes the colon as well)
=TRIM(RIGHT(M2,LEN(M2)-SEARCH("@#",SUBSTITUTE(M2,":","@#",LEN(M2)-LEN(SUBSTITUTE(M2,":",""))))))
Is it possible to modify it so that it only triggers if there is nothing after the last colon?
As you can see in the example below I have some notes entered by users and I would like to delete the ones where they didn't actually enter a note after the last colon:
11/05/2012 13:19:53 NEW MIKE : cld poe male sd cu not in till 3pm
10/18/2012 08:03:55 NEW HOSSEN : called no answer
10/15/2012 11:15:41 NEW HOSSEN :
10/12/2012 08:00:26 NEW HOSSEN :
10/11/2012 12:17:14 NEW HOSSEN : called no answer
11/14/2012 13:42:07 ACT MIKE : cld poe spk to cust sd cm back at 330
I would like to be left with just this instead:
11/05/2012 13:19:53 NEW MIKE : cld poe male sd cu not in till 3pm
10/18/2012 08:03:55 NEW HOSSEN : called no answer
10/11/2012 12:17:14 NEW HOSSEN : called no answer
11/14/2012 13:42:07 ACT MIKE : cld poe spk to cust sd cm back at 330
Link to article I found this formula in : [URL] .....
View 2 Replies
View Related
Apr 25, 2007
I have come up with this to Trim all of the data from rows 2:30 removing any trailing spaces after the last word in each cell. The macro takes a couple of minutes to run have I got something wrong that is making it run slowly or does the Trim process just take longer?
Sub TRIM_RANGE()
Dim myRange As Range
Dim myRow As Range
Sheets("CAMPAIGNS_2007").Select
Set myRange = Range("2:30")
If myRange Is Nothing Then Exit Sub
Application. ScreenUpdating = False
myRange.Replace What:=Chr(160), Replacement:=Chr(32), _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
For Each myRow In myRange.Columns
If Application. CountA(myRow) > 0 Then
myRow.TextToColumns Destination:=myRow(1), _
DataType:=xlFixedWidth, FieldInfo:=Array(0, 1)
End If
Next myRow
Application.ScreenUpdating = True
End Sub
View 8 Replies
View Related
Mar 9, 2009
I am trying to make a simple quiz on a spreadsheet which will provide the score immediately the player has finished. Basically a row is as follows:
C5= Question; D5 = player's input answer, E5 = check answer and give score
For example:
in C5 "What colour is a banana?"
The player inputs the answer to D5: yellow
in E5, the answer is tested by a formula and score is given. For this I have a formula IF(D5="yellow",1,0) hence E5 returns either 1 or 0.
Now my problem: If the player inputs one or more spaces, I need to use the TRIM function as well otherwise the answer will be incorrect. How can I work the TRIM with the IF formula?
A thought has just occurred to me that I might have to firstly Copy and TRIM the answer in to E5 and then have the IF condition in F5, but that needs another column and seems a longer way around.
View 2 Replies
View Related
Apr 30, 2012
I'm in need of a formula that would trim a name within a cell to look like this:
name in cell = Doe, John
Result looking for = DoeJoh
or
name in cell = Smith, Robert
Result looking for = SmiRob
The formula would take the first 3 letters of last name and combine them with the first 3 letters of the last name. There is a space after the , and the name would always be consistent with the exampel above.
Also what about a formula that would take a name like Doe, John and transpose it to John Doe.
View 2 Replies
View Related
May 1, 2014
In need of a formula to trim data:
Examples:
* 5 Extravagant Mak (nz) (100) Need the number plus a dot then space then the name, then minus everything after the name, so this would now read 5. Extravagant Mak thats 5dot space Extravagant Mak
*
11 Frisbee (100) This one would become 11. Frisbee thats 11dot space Frisbee
View 5 Replies
View Related
Jan 31, 2014
I'm coping and pasting data (html) into a worksheet. When I try to format some of the columns as accounting$, number, ect. They won't format. It looks as if there are spaces before and after the data. So I tried the trim and clean function but it doesn't work, so I guess the blank "spaces" are not really spaces.
It gets better...
When I do the same exact copy and paste (as html) on another persons computer, they are able to format just fine, and the "spaces" are removed automatically.
Why is this working on one computer but not the other? Is it a setting that my computer has enabled or disabled? If so, what is it?
View 1 Replies
View Related
Dec 30, 2013
I need to Index & Match the Max Value for 3 separate columns labeled "Price". The columns are not adjacent and cannot be moved. I am able to get the Max for the 3 Price columns using:
=MAX($L$12:$L$45,$O$12:$O$45,$R$12:$R$45).
However, I need the matching value in column D labeled "Int. SF". I can only do this for one column at a time so far using this formula
=INDEX($D$12:$D$45,MATCH(MAX($L$12:$L$45),$L$12:$L$45,0)).
When I try to use all 3 columns with Index & Match
=INDEX($D$12:$D$45,MATCH(MAX($L$12:$L$45,$O$12:$O$45,$R$12:$R$45),$L$12:$L$45&$O$12:$O$45&$R$12:$R$45,0)), #VALUE! is the answer.
If I enter it as an array, the answer is #N/A. I tried making the 3 columns into a table (which I named Price1) and tried the formula again
=INDEX($D$12:$D$45,MATCH(MAX(Price1),Price1,0)),
But that did not work either.
I tried using a V-Lookup, but can only get results for the first column, not the other two. I've attached my spreadsheet.
View 6 Replies
View Related
Mar 19, 2014
Comma separated data on sheet 2, look up info on sheet 1, return comma separated data on sheet 2.
Sample file attached : Book1.xlsx
View 3 Replies
View Related
Jan 7, 2009
If I have, in one cell (call it D1):
EH,DR,HU
and in a lookup table on another sheet:
A B
1 ED T
2 EH F
3 DR G
4 HU H
5 SE E
6 YU E
I need to be able to lookup the values in D1 on the table and return the values in column B to a single cell (say E1), also comma separated...
eg...
F,G,H
View 9 Replies
View Related
Dec 30, 2009
I am a basic excel user and what I want is to be able to add in a time and have the colon automatically entered for me. For example, I want to add 130 and have it come out to 1:30, or 1215 and have it come out to 12:15. Seems easy to me but I cannot figure it out.
View 10 Replies
View Related
Oct 20, 2009
I need to find a way to enter times into excel without having to use a colon.
The data I need in the cell is the hour, the minute, and either AM or PM. No seconds, which I believe is why Chip Pearson’s time VBA procedure doesn’t work for me, and I don’t know enough about VBA to change it for my needs.
So for example, I need to enter in times like 10:09 PM or 12:40 AM or 2:33 AM.
All my times will be entered into Column D.
I have done a ton of searching and tried several different solutions, but none of them allow me to put in AM or PM. If I just put 1009 into a cell with Chip Pearson’s macro, it will display as 10:09 AM, which is great if I need it to be AM. But if I need it to be PM, and I enter in “1009 p”, it will display as “1009 p”, neglecting the colon and not displaying the time correctly.
Does anyone have a solution where I can type in a 3 or 4 digit number (233 or 1240) and either “a” or “p” after it, and have it display as 2:33 PM or 12:40 AM?
I use Excel 2007, if it matters.
View 12 Replies
View Related
Aug 21, 2009
I downloaded some data from an extarnal source to excel... the texts I get appear as 'TEXT' (mind the colons at start and end of TEXT).
However, in some of the cells the Start Colon is visible and in some cells it isn't (although it's there and can be viewed in the formula bar).
I want the start colon to be visible.
View 27 Replies
View Related
Oct 3, 2006
Replaced the period with a colon, 8.23 resulted in 8:23. I have since deleted the Add In. Now every time I enter a "." it is replaced by a ":". This happens even when I disable macros on the spreadsheet. I cannot see any VBA code.
View 2 Replies
View Related
Oct 25, 2007
I am making a template for our security kiosk log and I wanted to simplify it as much as possible. I found the "time mask entry" and imputed that into the vba code. It was working for awhile however stopped working a few days ago. I went back and copied and pasted it again and changed the range to what I needed and an error came up stating "unable to set the NumberFormat property to the range class."
Private Sub Worksheet_Change(ByVal Target As Range)
Dim vVal
If Target.Cells.Count > 1 Then Exit Sub
If Intersect(Target, Range("A1:A100")) Is Nothing Then Exit Sub
With Target
vVal = Format(.Value, "0000")
If IsNumeric(vVal) And Len(vVal) = 4 Then
Application.EnableEvents = False
.Value = Left(vVal, 2) & ":" & Right(vVal, 2)
.NumberFormat = "[h]:mm"
End If
End With
Application.EnableEvents = True
End Sub
View 9 Replies
View Related
Jan 12, 2006
Is there any way that I can format a cell to insert a colon three spaces to
the left from the end of any group of numbers that I type in the a cell?
Examples 9:15 10:15
View 12 Replies
View Related
Jul 7, 2014
I have been sent a spreadsheet that was populated from another system. I need to run some VLOOKUPS but the cell that I need to compare has a ' in front of the text/number eg 'VB123456. I have found a thread on how to remove it for a number but it does not work with letters.
View 6 Replies
View Related
Nov 8, 2012
ColB is a time of day in 4 digits ****
I need it to be **:**
How do I get the colon in the middle?
View 1 Replies
View Related
Oct 28, 2007
I have used the format [hh]:mm in a cell for 24 hr clock calculations. Why do I have to enter the numbers with a colon when I populate the cells? Is there a way to set it up so I just type in the four numbers and the colon between the hours and minutes populates itself?
View 9 Replies
View Related
Mar 4, 2012
What is the best way to concatenate with a semi-colon across multiple cells in excel. The one kicker is I only want to concatenate where the cell is populated.
If I write a simple Concatenate or combined columns, if a cell is blank I get the multiple.
View 1 Replies
View Related
Nov 30, 2003
Is there a way to format cells so 24 hour time may be input without the colon, as an example 1425 instead or 14:25? We can do it in Access. One would think it possible in Excel, yet I have not discover how if indeed its possible.
View 9 Replies
View Related
May 6, 2009
Is there a way to enter a colon into a standard number to create a value that can be formatted into a 24 hour time value? eg a time is listed as 1345 with a general number format, and I want it returned as 13:45 witha custom format of hh:mm. Other than creating a table and using a vlookup function
View 3 Replies
View Related
Oct 4, 2011
i have encountered a problem which happens when you write data that contain : in an excel sheet (i use excel 2010)
for instance if i enter to one of the cell 45:58 excel sees it as 01/01/1900 21:48:00 when i try to get the information by using a function i will get the wrong data for example typing in the different cell LEFT(Cell,5) will result 1.908
(i receiving the data from an outside source in this way and i need to make analysis)
i have noticed that the first 2 digits (21 in the example) are related to the number i have choosen in a 24 hour cycle for instance
24:58 will result 01/01/1900 00:58:00
26:58 will result 01/01/1900 02:58:00
48:58 will result 02/01/1900 00:58:00
View 7 Replies
View Related
Jun 3, 2014
I am looking for VBA code that will save Excel 2010 files in semi-colon delimited format without having to chage the universal language options.
View 1 Replies
View Related
Dec 16, 2008
I would like to have a simple basic VBA trim function with no complications at all. Just a simple trim vba code like:
=trim( Selected/Active cells)
But I just couldn't think of how to do it, even with recording.
View 11 Replies
View Related
Aug 22, 2008
Sample of Column A:
Sat 15th Sep 07 14:15 Bath Rugby 29 - 15 Worcester Warriors Recreation Ground 10,010
Sat 15th Sep 07 15:30 Harlequins 35 - 27 London Irish Twickenham 39,400 Report
Sat 15th Sep 07 18:00 London Wasps 19 - 29 Saracens Twickenham 39,400 Report
Sun 16th Sep 07 15:00 Bristol Rugby 13 - 26 Leicester Tigers Memorial Stadium 8,125
Sun 16th Sep 07 15:00 Leeds Carnegie 24 - 49 Gloucester Rugby Headingley Carnegie
Sun 16th Sep 07 15:00 Newcastle Falcons 33 - 12 Sale Sharks Kingston Park 5,859 Report
Trying to display in column B and C:
Bath Rugby Worcester Warriors
Harlequins London Irish
London Wasps Saracens
etc....
Because of the varying length of text each time,
i'm having problems doing this.
Tried =MID(A1,FIND(":",A1)+4,25)
but obviously get extra text other than team name.
View 9 Replies
View Related
Jun 21, 2014
I want to be able copy certain things from cells. Is there a way to make excel copy only the name from cells containing this (One name in each cell):
VESNINA E. (RUS)
PAVLYUCHEN. A. (RUS)
KERBER A. (GER)
I basically want to cut out the (...) in all names. I could do it manually, but there could be quite a few players.
View 3 Replies
View Related
Jun 25, 2009
I would like to have in a title cell "The Week of 8/24/09 - 8/28/09". I want to use something like this
View 2 Replies
View Related
Dec 18, 2008
I want to create a macro that will allow me to highlight a column and have the macro Trim every cell with text in the column, preferably putting the results over the original text. The column in question has text in every cell, until the column ends.
(That is, there are no numbers and no blanks until the data ends altogether.) I do not want to remove internal spaces in the text, just the leading and trailing ones.
I am sure this is fairly simple, but I'm not sure how to get it to look at every cell and then terminate properly.
View 8 Replies
View Related
