i just wanna extract the date shown(last data until meet the first comma) in first column to another column.
column 1
abcde, 1234
abcd, 12345
abcdef, 123
column 2
1234
12345
123
Imagine that I have this list of thousands of customers, who are listed in column B in the format "customer no+space+customer name".
Now I want to extract ONLY the customer no from the text string in column B and insert it as number value in column A on the same row.
Would have been easy with "Left" command, but as the customer numbers have variable length, I have a problem.
I have the following issue. I have a list of strings. Each string contains certain characters that are exactly the same for all strings, some characters are different making the string longer (in some cases). What I need to do is extract some combination of characters from each string. The strings look like this:
AB & CDE & FG & I mmmm yyyy.HIJK
AB & CDE & FG & II mmmm yyyy.HIJK
The part "AB & CDE & FG & " (incl. spaces) is the same for each string. The next part contains a roman count from I to VII, causing the length of each string to vary. The "mmmm" part contains the current month spelled in full e.g. December. This part differs as well, for each string. The "yyyy" part contains the year in four digits, e.g. 2007. The part after the dot is the same for each string again.
What I need to do:
- is to extract the month and assign it to a new string
- extract the year and assign it to a new string
- extract the roman number, translate it to a normal number (II -> 2) and assign to a string (or integer).
I am trying to split up a cell into numbers and charachters and place them in
separate columns, but the lenght of the number part varies as does the
content of the character part.
For example, one cell could be 5#, 10Tins, 4 lb, 100Pcs, etc.
I would like to be able to pull out the #, Tins, lb, and Pcs in to their
own column.
I have found this formula:
=LEFT(A1,FIND("-",A1,1)-1)
but it assumes some level of consistency, the "-" in the cell.
Hey I got a long String like this "[...] increase of x.xx% [...]".
I am trying to extract only the percentage number which can be of variable length, so maybe 900.99% or 9.99%.
I tried this formula:
=MID(G14,SEARCH("%",G14)-5,5)
but this one doesnt bring the right results as the percentage figure is often not exactly 5 characters long.
(I need to do this using VBA so that it can work as an excel macro and/or a vba function in access.)
How can I extract the first occurance of any substring over a certain character length from within a string ?
eg. My string may take any of the forms given below (or something similar), how can I extract the first set of characters that is at least (say) 4 characters long ? i.e. always get the 'Phillips' out of the below...
Phillips Homes Ltd
Mr T A Phillips
TA Phillips Homes Ltd
T A Phillips Homes Ltd
T. A. Phillips Homes Ltd
The names used are only an example (Phillips could be any name whatsoever).I am familiar with Search, Len, Left, Right, Mid, Instr etc. but just can't figure this one out.
I've set up a filing system which saves sheets/ workbooks based on the value of a cell - Range("B1") Everything works great apart from when ThisFile String length exceeds 31 characters which you may know is the max useable character length for a sheet name - I had no idea! 8-0
Is there a way i can check if string length exceeds 31 characters then, if it does, shorten it to 31 characters?
[Code] .....
I have a long string that has a list of digits in the middle that I want to extract. The string is variable length and the number of digits I want to extract is 5-7. I also have slashes in between the numbers I want to extract. I need a UDF that allows me to extract the 5-7 digit number from the string and restrict around the slashes (i.e. if two sets of digits in the string match the condition for extraction, extract the one around the slashes.) For example my original data is like
1. aaa/12345/aaa/123
2. aaaaa/123456/aaaaaa/3423
3. 323/aa/1234567/aa
and I want for results
1. 12345
2. 123456
3. 1234567
Is there a UDF that allows me to do this?
My worksheet contains data with the reaction times on a psychological test. Each respondent in the test has 280 rows in my excel sheet.
The 'perfect' length of the row, is from A to M. When an error is made in the test, the length of the row will increase. So the error length can be A to AA.
For me it is important to analyse the error. So I would like to give a perfect row length, the value 1, and an error row length a value 2.
So, in conclusion:
If:
Cell length = A1 - M1? --> Copy A1 B1 C1 (A B Cof that row) to Sheet3, and give D1 in sheet 3 the value 1
Cell length >= A1 - M1? --> Copy A1 B1 C1 (A B C of that row) to Sheet3, and give D1 in sheet 3 the value 2
I've found several posts about returning variable substrings that dealt with one or two spaces, but I have not been able to find anything that is for multiple spaces. The number of characters before the substring will remain constant.
For example:
Income from transmission agreement - 83 subs @ $0.44
In this case I need to extract: 83
Income from transmission agreement - 10,312 subs @ $0.50
Need: 10,312
I am trying to create a string of text that grabs info from other cells, which is easy so for example
=A1&A2&A3
would grab all the info from the 3 cells and merge them to create a string, but what I want to do is create a prefixed length.
Lets say A1, A2 and A3 would equal 10 characters in total but B1, B2 and B3 would equal 7 characters, these would be different lengths. so for a visual description I would like them to appear like this
At the moment
01MAIN-HELLO
01DESSERT-HELLO WORLD
Would like
01MAINXXX-HELLO
01DESSERT-HELLO WORLD
This would make both strings the same length and would make it easier for me to export to a custom file that needs a certain length prefix.
I have a row of 1's and blanks, where there might be 10 consecutive 1's, followed by four consecutive blanks. I want to calculate the average length of a string of 1's in the row -- how this might be accomplished?
View 7 Replies View RelatedSo I have got an identifier and a corresponding column of values. I need to calculate sum of those corresponding values for each identifier, so if identifier is 1993, I need to have a sum of all the corresponding values. These identifiers repeat, and I do not want to calculate sum for all of them, just the immediate identifiers (i.e. you have 1993, after that you may have 1994 etc etc and then at one point you will have 1993 again, but that "new" 1993 will have a different sum of course). Also, identifiers do not go in sequence sometimes (however, most of the case they do), for example 1993 may jump suddenly to 2004. The trick with all of this is variable "row width" of these identifiers...
222.xlsm
a formula to extract the numbers into two separate fields. The text may vary in length and the numbers vary in length also from 1 digit to 2,000,000.
Data in A1 is as follows:-
Meter reading Old:1345 New:67890
I get all my results from a CSV file. This isn’t a problem except for the first nine days of the month where excel cuts off the first 0 so 01012009 reads 1012009 and cannot be properly concatenated.
DateNameItem
1012009BobKeyboard
1012009SidMouse
1012009DaveMonitor
1012009EdMonitor
1012009AndrewKeyboard
1012009DanKeyboard
1012009BruceKeyboard
What I would like to do is have VB read the contents of the date cell and if it is less than eight characters, edit the cell by adding a zero at the beginning of the string. This needs to be a one off process for all cells. Can anyone come up with code that might make this happen?
I have an ODBC connection to a db2 database in MSQuery. I want to return the length of a string. I've tried Len(string) but it says its not in the library.
View 2 Replies View RelatedThe following afterupdate procedure for my txtStart1 text box sends the value to the FormatTimeValue function that is coded in a module.
Private Sub txtStart1_AfterUpdate()
MsgBox Len(Trim(Me.txtStart1.vaue)) '<= outputs 1
Me.txtStart1.Value = FormatTimeValue(Trim(Me.txtStart1.Value))
End Sub
As you can see from the bolded text, the length of the value is 1 when outputted from the forms code.
However, when I pass it to the following function in a module it outputs 2 as the length.
Function FormatTimeValue(vTarget As Integer) As String
Dim TimeStr As String
If IsNumeric(vTarget) Then
MsgBox Len(vTarget) '<= outputs 2
Select Case Len(vTarget)
Case 1 ' e.g., user entered 1 so time should be 01:00
TimeStr = "0" & vTarget & ":00"
I have looked into the maximum length of a variant/string in vba and it appears to be 250 characters. I am running a macro which first lists all excel files in a folder, returning them to a sheet, then using a loop statement opens each one in turn extracting the information to a second summary sheet before closing it. The file path to the folder is ridiculously long and the macro stumbles. I used the =LEN(A1) formula to check if the file names were too long for the string, but the maximum file name length was 226 characters. I've tried both String and Variant to collect the file names but both have the same effect.
View 2 Replies View RelatedI need to insert 00 if the string of text = 13 in a specific column.
eg. Column K can contain any amount of lines of data. The data will either be 15 or 13 characters in length. If its length is 15 its fine, if its 13 I need to get it to 15 characters.
The first 6 characters are letters so if the length is only 13 I need to add the 00 in after the last letter.
how can I make this simple macro sum columns that will be of variable length? I would like the sum to appear as the last entry in that column and highlighted.
VB:
Range("H36").Select
Selection.FormulaR1C1 = "=SUM(R[-34]C:R[-1]C)"
Range("J36").Select [code]....
Column M:
##/##/#### | Variable Length Text-####
Example:
01/06/2014 | Daniel Trimble-4048
I need to parse out the different parts of Column M.
In Column R -- "Close Date", I'm successfully using:
=LEFT(M2,FIND(" | ",M2)-1)
...to extract the close date of the donation.
In Column S, I want to list the donor name--which is all of the text after " | ", and before the "-".
I don't need anything after the hyphen, and fortunately in this data, no one's name has a hyphen in it.
The Close Date is working fine for the LEFT and FIND functions, but for the life of me, I can't seem to get MID to work for the variable-length text. The text will always start in the same position -- 14, as the date and delimiter are standardized. And the last 5 characters of the text are not variable in length, so they can be cut out completely.
How do I use MID to extract everything starting at position 14, and stopping 5 characters short of the end of the text?
I have a SS I would like to reference a cell in a column which changes in
length. In this case, I would like to reference the most current date in a
column.
Is there a simple method for doing this, as the data will routinely be
different lengths in that column.
Further, there is Data on the Horizontal that I would like to reference to
another cell in another sheet that would correspond to this date. Can anyone
give any suggestions as to the best way to accomdate this?
I am trying to do is to build an array from a series of cells that may or may not contain text.
For example cells A1:A6 may be equal to red, orange, yellow, green, blue and purple,
but each cell may also be blank.
So I might also have blank, blank, yellow, blank, blue, purple.
Is there a way I can make an array of just {yellow, blue, purple}? and then the next time it may be {red, blue}
Or can I index only cells that have text?
In the end, I just need a way to make a selection from only those cells that have text.
I require vba code which will allow me to select a range of variable size.
Starting from cell A2 use :
Range(Selection, Selection.End(xlDown)).Select
However then using the line below may not select the entire range as some columns may be empty
Range(Selection, Selection.End(xlToRight)).Select
I have a range of data starting in C18, C17 is a label (DivsUsed) the number of rows can range from 1 to a few hundred.
I used the code below to name C18 onwards as a named range with the name DivsUsed and then to use this as a RowSource for a ListBox.
Private Sub UserForm_Initialize()
Dim rangeToName As Range
'Sheet2.Range("C18", Range("C65536").End(xlUp)).Name = "DivsUsed" Tried this too
Set rangeToName = Sheet2.Range("C17", Range("C65536").End(xlUp))
rangeToName.CreateNames Top:=True
ListBox2.RowSource = "DivsUsed"
TextBox2.Value = Sheet2.Range("F2").Value 'This works ok
End Sub
Both tries, and anything else I have tried, give the error " Method 'Range' of object _Worksheet failed".
I have a set of data in Which in column A is the name of organisation.
If string in col A is longer than 50 I need to split in and put in col B.
That would be simple however I need to do it in a smart manner: i.e. cut it to the nearest full word.
Example:
THIS EXAMPLE NAME IS TOO LONG TO FIT INTO 50-TEXT CRITERIA SO I NEED TO DIVIDE IT INTO TWO STRINGS
Incorrect; length = 98
THIS EXAMPLE NAME IS TOO LONG TO FIT INTO 50-TEXT
Correct; trimmed down to 48.
My question is about formula that can detect spaces and depending on those trim the string down adequatly:
to 50 if 50th char is preceeded by space; if not then check where is the next space going towards left. Once you find it cut the string there.
What formula I can use to get desired effect?
I have a spreadsheet with 2 sheets. The first just links to another file and pulls in the contents of a particular sheet, cell for cell. The second sheet is a formatted report that I'm creating to put that data into a different format for presenting to others. I'm using the Index and Match function along with range names. All is fine except where the string that should be returned is very long. When that happens, I get "######" all the way across the cell. It doesn't matter how wide I make the column or how tall I make the row, the "#s" do not go away. Is there a limit on the number of characters the Index function can return?
View 5 Replies View RelatedI'm trying to sort a report using case number in Ascending order, this report varies in length and data begins in cell B2 down the whole report. It needs to include the fields on either side ie. expand the selection.
View 3 Replies View RelatedI have an issue regarding use of conditional formatting where the range i wish to format is on variable length.
For example; lets say i want to format F15 downwards depending on there being a value in the cell, how could I do that ?
Lets say I want the cell to turn GREY if number is 0, RED if the number is below 85, GREEN if above 95 and AMBER otherwise.
How could I do that ?
Any how could i ensure it picks all rows in the range where numbers have been added
My key is in row A (up to 50,000 keys) my entries are in column B. There could be between 1 to 20 entries per key (variable). i need to move the entries across so my key remains in column A and the entries appear in columns B, C, D, etc. Need to end with one row per key.
from this:
823
car
pool
window
345
pen
123
pen
[code].....