Strip Chars From Cells & Sort
Feb 19, 2008
I have just done a vba project of striping chars (QOOO1=) from each cell
and then sorting the records on column6/question6
however i have been able to do this and display results in second worksheet(Results2)
I feel there is a better way to do it and display the results on same worksheet
Could anyone take a look at the file and help.
IMPORTANT:Place curse on "Results2" worksheet and run the macro:Pres
Do not run the macro on main sheet all data will be lost and will have to download again.
File:[url]
View 6 Replies
ADVERTISEMENT
May 16, 2006
I have a problem with a spreadsheet my colleague at work has designed. He has entered all the names into it as Mrs A Jones, Mr P Smith, Mrs S Pilkington-Smyth etc. I need to sort these into Alpha order using just the surname. macro or a formula to count backwards until I get a space and then strip this off to allow me to sort it.
View 2 Replies
View Related
Mar 26, 2009
Assume someone typed the hereunder date ad TEXT.
The Formula bar shows: ="01/01/2009"
The cell presents: 01/01/2009
I need to remove the equation sign as well the two inverted-commas.
I am familiar with the "Text to Column" feature, the use of SUBSTITUTE Function, a short macro and also the Find&Replace is also a good idea but, as far as I understand,
it must be run Twice(!)
I am looking for a way to use Find&Replace in "One! shot" - meaning, to put the TWO different(!) characters [ the '=' and the " ] in the 'find what' window,
leaving the 'replace with' window empty.
I know that something similar to that can be done in a "Word" document - but can it be accomplished in Excel?
View 6 Replies
View Related
Dec 24, 2009
I believe I should use the INSTR function, but I simply dont understand its use very well. I have a string in a cell, "MEAS/SPHERE,F(QUA_1),5", the characters I need start at the "(" and end at the closing ")", ref: (QUA_1). The start and end will always be the same, the length of characters and the spelling of characters be anything. Thus, QUA_1 can be CAL_1, CAL_2, SOMEOTHERTEXT_1 or 187bgo_199, etc, always enclosed in "( )". I wish to place the characters and the closing "( )" into a variable as a string.
View 5 Replies
View Related
Jan 8, 2010
CALIB/SENS,S(S5a90b-90),FA(CAL_15)
This one has two sets of quotes, with the information from the noted post I can retrieve the string "S5a09b-90", now that I have a double set of brackets "( )" how do I get the value for the second set of brackets, "CAL_15"?
In the quoted string, CALIB/SENS,S, is constant. The first set of brackets are constant (string length is not), the second set of brackets are constant (string length is not).
View 7 Replies
View Related
May 21, 2009
I made in a macros this formula :LEFT(RC[-1],(LEN(RC[-1])-2))
is removing from left side an index , like in this example '
9117 - 570-65CE49D-B4-7 to obtain 9117 - 570-65CE49D-B4
so here is my code,
is with an userform, but it gaves me an error, that my procedure is invalid
View 5 Replies
View Related
Jan 11, 2007
I need to some spaces to the end of a string to make it up to 36 chars.
The original string can vary in size based on the courier name.
i.e. : business direct invoice 40617 = 29 chars
i.e. : TNT post invoice 4061755 = 24 chars
Can someone point me in the right direction to be able to concat' some spaces on the end to make it up to 36 chars.
Would this be easier done using VB?
View 11 Replies
View Related
Jun 22, 2006
I want to remove varying amounts of periods that appear at the end of my strings. For instance, ABCEDFGH......., DEFGH.., FG.... etc. Do I have to test each char to see if it's a period, and then use that count of chars to LEFT out the string that I want? Or is there an easier, quicker way to do this?
View 3 Replies
View Related
Aug 23, 2014
I'm looking for a formula which returns the longest consecutive series of occurrences of 2 chars in a text string.
In the case I'm trying to count the longest consecutive number of nine-spares (9/) in a bowling game.
For example:
|7/|X|9/|9/|9-|9/|9/|9/|9/|X8/|longest series of 9/ would be 4.
|9/|9-|9/|9/|7/|9-|(8)/|X|7/|9-|longest series of 9/ would be 2.
|9/|(8)1|X|X|72|63|7/|8/|8/|9/9|longest series of 9/ would be 1.
View 9 Replies
View Related
Dec 17, 2009
I have a file of names that has some undisplayable characters. I am trying to match against a different file. It works if I overtype the "blank" fields with a space. How can remove these trailing problem characters whatever they are? I tried CLEAN and TRIM and the mystery characters are still there.
View 2 Replies
View Related
Oct 27, 2009
I am having problems with a macro I am writing and I need some guidance on where I am going wrong (I am still a noob at VBA).
I have pasted the code below (not the most efficient use of code I know).
I have a list of departments and codes from the Intranet at Work. Now this contains a Department Code, Department Description and within the Description is a number in brackets showing how high up the organisation hierarchy they are....
View 9 Replies
View Related
May 30, 2007
I'm using excel 2003 and I have have a dynamic string of data separated by 19 commas ",". I think 19 (the # of commas) is one of the few fix numbers...
What I'd like to do is from Right2Left return the 5 characters immediately to the right of (before) the 11th "," comma (i.e. 22.59 for the 1st string on Excel Cell A2) OR from the Left2Right return the 5 characters immediately after the 9th comma "," comma, which is also 22.59
Example of some of the strings I've been trying to work with...the list is much longer...but for example sake I've limited to 4...
View 9 Replies
View Related
Mar 3, 2014
how to strip the time from a date formated field using "=INT()". At the moment this works fine for me but I wonder if there is a possibility to strip the day form the datefield as well.
View 10 Replies
View Related
Jul 9, 2013
I have a range of cells where I need to strip out the numbers using a formula, is this possible?
E.g.
The data is:
110205 Contract Revenues
110215 Job Revenue
110802 Consumables
This would need to be:
Contract Revenues
Job Revenue
Consumables
View 13 Replies
View Related
Oct 22, 2007
I have a spreadsheet which has a "User Description" column. In this column, most users have data that looks similar to this:
APPLICATION - john.smith@mycompany.com
APPWEB - jane-doe@somecompany.com
APPWEB - john_doe@othercompany.com
APPLICATION - TP - paul@acompany.com
APP/APPWeb/DEV - paulsm@thecompany.com
There are also cells in this column which literally have a sentence long description, but those are not my focus. What I need to do is find a way to strip out the e-mail address found in the examples above and copy them into a new cell (the same row for the corresponding user). I don't know where to begin for this.
View 9 Replies
View Related
Jun 28, 2009
Excel 2003
I have two columns of Data:
Column A Origin
Column B Destination
Each column contains:
KANSAS CITY MO 25300 RICHMOND CA 11300
I would like to strip out the 5-digit code
My worksheet is named "data"
View 9 Replies
View Related
May 26, 2006
what is the differance between a multi page control and a tab strip control
View 4 Replies
View Related
Jul 25, 2014
How can I strip out a name from a cell, see example below, I would need to remove the number and flip the name.
example:
Column A = Column B results
Blow/Joe 123456 = Joe Blow
Johnson/Mike 898930 = Mike Johnson
Mama/Joe 293810 = Joe Mama
Smith/John 389301 = John Smith
View 14 Replies
View Related
Jan 13, 2014
I've a column of data as below:
ROACH (#21)
SHARK (#11)
SHARK (#13)
UNICORN (#26)
ROACH (#20)
TRIGGER (#09)
UNICORN (#06)
UNICORN (#09)
UNICORN (#11)
VIMBA (#03)
VIMBA (#04)
TRIGGER (#06)
ROACH (#07)
ROACH (#08)
ROACH (#10), UNICORN (#24)
What I need to do it to strip off all the characters after the first space.
I assume I can use FIND and then LEFT, but how to I put this into a Macro?
View 4 Replies
View Related
Jun 19, 2009
What are the differences/advantages to these 2 controls in a User Form?
I have already seen that the tab strip doesn't seem to toggle b/t pages, as a frame I placed remained on both "tabs" (I am wondering what it does if you can't toggle).
View 9 Replies
View Related
May 17, 2007
formula to strip apostrophe for lookup formula to recognize text as a number.
View 9 Replies
View Related
Feb 7, 2014
Exported data in PDF and converted to Excel worksheet and some of the data contains a space in the year.
Example: 2/6/2 014 2:49 PM.
I used the =INT(A1) to strip the time off. Worked wonderfully. However when I need to sort by ascending/descending date , it does not put in order due to this extra space. Is there a formula out there that I can use to delete the space? All of the data doesnt appear this way. I am sure it is due to the conversion.
View 9 Replies
View Related
Jun 20, 2014
I want to strip only the leading set of numbers from a string. Trailing numbers should stay.
Logic rule would be:
1) Find first Alpha character
2) Remove all numbers prior to this position
Code:
Original string Desired string
42114DEP DEP
050114OP2OP2
0514OPOP
050214BKCKSBKCKS
05214CKCK
0502CKSCKS
050214CKS-3CKS-3
050214CKSACKSA
050214OPOP
50214PSCASH1PSCASH1
050214PSCKSPSCKS
0502SALES SALES
View 9 Replies
View Related
Jan 31, 2008
I need VBA to remove more than 3 blank spaces after the city & replace them with a comma & one blank. For example:
All in one column AB starting with row 7 I have:
Johnson City (13 Blank Spaces) NC 75503 (1 to 3 spaces after state & is OK)
Jacksonville (10 blank spaces) Fl 23854
Henderson (12 blank spaces) NH 28363 (12 blank spaces)
etc
I need all in one column AC starting with row 7:
Johnson City, NC 75503 (,space before state, same space after state ok)
Jacksonville, FL 23854
Henderson, NH 28363
etc
View 9 Replies
View Related
Sep 8, 2009
Is there an Excel function that can strip a specificed unwanted character from a text string to leave all the other characters excluding the unwanted one?
e.g. if the original text strings were (in separate cells within a workbook):
C-2C-9C-9-XA-10A-10-BA-10-XA-8N-7-D
The function would strip the "-" to leave:
C2
C9
C9X
A10
A10B
A10X
A8
N7D ...etc.
View 9 Replies
View Related
Apr 27, 2007
I have one hundred rows of data and within that there are some records which are the same and they will always be in pairs - identifiable by a cell with the same ref common to both records. how to strip out the records that are not part of a pair? I would like remove all of the non pairs and move them to another sheet.
View 2 Replies
View Related
Nov 11, 2013
I have data on 400 rows. Each row has a maximum of 10 cells with data, but many have empty cells with no data. I would like to sort each row to show values of cells in sequence and eliminate empty cells. I can use the sort row function but its a long process for 400 individual rows. Is there an easier way?
View 1 Replies
View Related
Nov 26, 2012
What is the formula for results in C?
A | B | C
NAME|RNK|SORT
Frank|2|David
Jane |3|Frank
Lisa |5|Jane
David|1|John
John |4|Lisa
Abby |6|Abby
View 3 Replies
View Related
Jan 1, 2005
How do you sort a column of numbers with hyphens?
Example #####-###-####.
I want to sort by the 3 middle numbers
View 15 Replies
View Related
Feb 22, 2010
i have a problem to sort merged cells in the file attached. i tried via macro but could not reached a good solution. I have 30 plans inside the sheet and even manually is too hard to do it because i use some "=" to not type again every number since some are the same but when i try to sort the lines became diferent values.
View 10 Replies
View Related
