How To Return Specific Chars In A Dynamic String
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
ADVERTISEMENT
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
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 22, 2013
I have the following type of info in A1,A2,A3...
Code:
nameGaryage40cityPittsburgheight190
age30height150
ameLindacityMichigan
citySan Jose
ameHarryheight180age50
My goal is to get as close as possible to this,so it will be easier to sort and manage
Code:
nameGaryage40cityPittsburgheight190
nameLindaage30cityMichiganheight150
nameHarryage50citySan Joseheight180
I can't use the "" sign as delimiter to separate them into different columns because the age,city,name and height fields are in random positions on different cells.The good thing is person's name will always come after "name" string, age is alwals followed by "age" string, so it cannot be like nameheight40Michigan180
I think the following would be the easiest method(not for me tho).If on B1 I had a formula that said "find the string "name" and write anything after it until you reach the next "" character".On C1 field I could have a formula "find the string "age" and write anything after it until you reach the next "" character.On D1 I would have the same for "height" string,then on E1 for city string.
My question is somewhat similar to this one
Extract A String Between Two Characters
Formula which outputs the data between 3rd and 4th instances of the "_" character.Can we substitute "3rd and 4th" with a specific strings like "age" or "height" ?
Code:
=TRIM(LEFT(SUBSTITUTE(MID(A1,FIND("|",SUBSTITUTE(A1,"_","|",3))+1,LEN(A1)),"_",REPT(" ",LEN(A1))),LEN(A1)))
View 3 Replies
View Related
Jan 31, 2008
Find a short text string in a column of longer text strings and when that short text string is found return the longer text string that matches.
View 3 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
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
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
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
Jul 9, 2014
​01112
2
02111
03111
04111
05111
06114
2
3
4
07111
08112
2
09111
10112
2
Clomun-1: Numbers written serially from 1-10 based on some logic.
Column-2: Start from non empty column, increment if next column is empty and increment until a non-empty column found.
Column-3: The row where max number need to be written.
column-4: Expected max number to be written from column-2.
I can managed to write max number on the same row where column have the max number in the range. As you can see column-2 dynamic size of range need to be used by the max function. I can solve the problem using macro but trying solve using cell formula.
View 1 Replies
View Related
Jul 9, 2014
1
1
1
2
2
2
1
1
1
3
1
1
1
[Code] .....
Clomun-1: Numbers written serially from 1-10 based on some logic.
Column-2: Start from non empty column, increment if next column is empty and increment until a non-empty column found.
Column-3: The row where max number need to be written.
column-4: Expected max number to be written from column-2.
I can managed to write max number on the same row where column have the max number in the range. As you can see column-2 dynamic size of range need to be used by the max function.
View 4 Replies
View Related
Dec 21, 2011
I am getting an error i have list (sheet4) i want to lookup the data from different table (Name range) based on the location in column A i am using the small function copy down, in cell C6 i want the range ROWS($A$2:B6) go back to ROWS($A$2:B2) since the criteria in column A has changed from China (A5) to US (A6)
Sheet4ABC1Locationproductsamount2ChinaA2954.63ChinaB1565.284ChinaC3772.055ChinaD2027.76US#REF!#REF!
Array FormulasCellFormulaB2 =INDEX(INDIRECT(A2),SMALL(ROW(INDIRECT("2:10"))-ROW($A$2)+2,ROWS($A$2:A2)),1)C2 =INDEX(INDIRECT(A2),SMALL(ROW(INDIRECT("2:10"))-ROW($A$2)+2,ROWS($A$2:B2)),2)B3
[Code].....
View 1 Replies
View Related
Jul 16, 2014
I need to sum a specific range of values between two headers in several dynamic sheets. The values in the sheets always starts in row 10 and column H (Forcast). For example the header could be "Sales" in column A. Then I want to sum values above the header "Sales" in forcast column H. The next header could be "Operations", where I want to sum the values between "Sales" and "Operations" in column H. Next header could be "Rents" then I want to sum the values between "Operations" and "Rents" in column H, and this is how it continues... Is there any macros for this?
View 2 Replies
View Related
Oct 21, 2009
I basically have this worksheet which has Cell D3 Being Dynamic. It puts the lowest cost supplier from columns H:W. If i change the prices in H:W it will bring up the lowest cost supplier in D3. Once D3 is chosen i want it to self populate F3 and G3 based off of what is in D3. This data should be pulled off from what is in the H:W columnns corresponding to the supplier in D3.
View 3 Replies
View Related
Feb 2, 2010
I am a personal trainer/strength coach, and i use excel to write my workout programs.
To save time in writing programs i have made lists of all of my exercises in a separate workbook. I have them all linked via data validation in drop down lists to different templates for different populations ;weight loss, injury prevention, sports performance, ect. And it has been working great!
However here is my problem, I would like to be able to return an entire list of an exercise circuit with one drop down list and have it input all the exercises in cells below the drop down list.
So for example i have three columns on a separate sheet.
Column one
Circuit A
exercise 1
exercise 2
exercise 3
Column two
Circuit A
exercise 4
exercise 5
exercise 6
Column Three
Circuit A
exercise 7
exercise 8
exercise 9
In cell A1 i want a drop down list that that has circuit A, B, C,
using the drop down list if i pick circuit A it will return exercise 1, 2, 3 in the cells A2, A3, A4, receptively.
View 9 Replies
View Related
Jun 17, 2014
I was having trouble on getting a text string within a sentence..
Example:
In column A1:
1 - AMERICA 85 - 90 2 - CHINA
So I want to get only the 85 - 90 and it will shows on column A3..
View 9 Replies
View Related
Aug 3, 2009
I have a string in one of the following formats:
Banana 2, Orange 5, Lemon 0
Banana 7, orange/Lemon 9, cucumber 6
Melon/grape 3, Pineapple/ Orange 1
Banana 1, orangefruit/Lemon 2, pine 8
I would like to take out the first integer that comes after the word Orange (not case sensitive). I'm kinda at a loss here, how do I go about accomplishing this?
View 9 Replies
View Related
Oct 25, 2012
How does one extract a specific sting/words from each cell? Especially if [formatted data] varys in characters (not suitable for regular LEFT, MID, RIGHT functions use).
Sample Question.xlsx
View 3 Replies
View Related
Jan 28, 2014
I'm using the code snippet below to add text and apply formatting to a cell within a given sheet.
[Code] .....
I'm now trying to amend this so, the word "All" is in bold text and I've recorded the a macro, and have the following:
[Code] ....
The problem is, is that when I try to amalgamate the two, I'm unable to get this to work.
View 8 Replies
View Related
Feb 12, 2009
I want to delete a specific words from string but i have a problem with the code below. For example, i wan to delete the word "Inc" only but the problem with my code is that it is deleting from "Incorporated" too and i want only the code to delete only if it finds the word "Inc" only.
View 11 Replies
View Related
Oct 12, 2009
I have absolutely no idea how to get starting on this one. I've got a long string in cell B1. At some point there is the word "oms:SomeString," (without the quotes). I need to know whether SomeString is somewhere in the active sheet or not (the workbook running the VBA-code is not the active sheet). I can't just compare the cell B1 because it contains multiple words. Any hints are very welcome.
View 3 Replies
View Related
Sep 7, 2009
Lets say I have a 2 column sheet (A and B). And I want to sort on column B with all the rows that contain a given string, i.e. /g or Δείτε.
Something like doing a find all but then being able to select/copy/cut etc those rows.
How can this be done?
View 11 Replies
View Related
May 28, 2012
I want to filter columns from an excel spreadsheet where the value in the cells contains/includes the String "Create".
View 3 Replies
View Related
Nov 24, 2006
I want to search a string for specific characters.
f.e. Begin = "bfPaa2"
I want to look for "P"
So, the answer has to be: Letter = "P" after searching the string
View 9 Replies
View Related
May 27, 2009
I need to refer the LAST ROW OF COLUMN "D" to appear in the message box for the below code along with " Receipt number" which is in Sheet2.
Sub saveit()
With Sheets(2)
r = .Range("B65536").End(xlUp).Row + 1
InvN = Cells(15, 4).Text
If Range("c18") = "" Or Range("c20") = "" Or Range("c20") = "" Or Range("c24") = "" Then
MsgBox "Please fill all required fields", vbCritical, " Missing data"
Exit Sub
End If...............................
View 9 Replies
View Related
Sep 5, 2009
In one column I have different objects separated by a comma. I need to select one of these: 11,20,30,60,61 and copy it into another column. I have used this
For counter = 0 To not_empty_cells
For counter_dep = 1 To 5
position = InStr(1, (Cells(counter + 3, 4)), department(counter_dep))
If position > 0 Then
symbol_dep = Mid(Cells(counter + 3, 4), position, 2)
Cells(counter + 3, 10).Value = symbol_dep
End If
Next counter_dep
Next counter
It works, however, once in the first column there are the following objects: 60,6128,CZ, it takes 61 but it should take 60. Unfortunately, the position of the object can vary, it is not always on the first position.
View 9 Replies
View Related
