To Have Each Data String In Its Own Row
Jan 22, 2008
S 03 01/22/08 03:09:55 3 ---- ----
A 03 01/22/08 03:10:01 4 .175 .175
P 03 01/22/08 03:10:15 3 ---- ----
A 03 01/22/08 03:19:09 3 .107 .107
A 03 01/22/08 03:19:34 4 .360 .360
A 03 01/22/08 03:19:53 1 .157 .157
the first single letter is the test evaluation code - "A" for accept - "P" for Stopped - "S" Severe Leak - Among others.
the "03" stands for the program number which there are three of - "01" "02" "03" --- These can be changed at anytime but by the user of the tester.
then of course the date
the the time - military style
the next number represents the test station - station 1,2,3, or 4
and the last group stands for the "leak rate"
------------
A couple questions I have...
1.) right now all the data is being placed into Cell A1 only. I need to have each data string in its own row. How can this be done.
2.) It would be great if I would be able to break up the string into separate columns. -- Like "P" in column A. "03" in column B. date in column C. etc...
3.) And maybe if those are simple enough to accomplish would I be able to separate into different sheets? preferable separate them by the "station number" which is the 5th group in the data string.
View 9 Replies
ADVERTISEMENT
Aug 21, 2014
I have 3 columns.
Column A = 1 d 03:32:42
Column B = MID(A2,1,FIND(" d ",A2,1))
Column C = IF(OR(B2 > 2, B2 = 2)," 2 days or more", " OK")
I used MID(A2,1,FIND(" d ",A2,1)) to extract the days out of a a Date + Time string.
I got column B (days)correct. But in Column C,this does not seems to work. ( where i want to indicate if it is OK or EQ /more than 2 days.)
=IF(OR(B2 > 2, B2 = 2)," 2 days or more", " OK"
View 3 Replies
View Related
May 6, 2008
I have a branch name in column D, which varies in length and want to combine this with Text
For eg C672 may contain Bedforview and I want to combine this with Net profit Workings
I have tried to use the following formula, but it comes up as text. I also need to incorporate the len function as the length of the regions vary in column C
="&(left(C672,10) "&"Net Profit Workings"
View 9 Replies
View Related
Mar 31, 2014
I maintain a spreadsheet to track monthly sales of a few thousand items (see attached sample data). I'd like to have a formula that would sum only the last 12 months in the range of data. It would need to ignore all of the data before and the blank cells after the 12 months.
It's difficult to update the range each month for all of the products.
View 5 Replies
View Related
Sep 7, 2009
I have a string (as below - Call them A1:A4) which I would like to seperate into 4 columns (Call them B1:E4).
I have successfully seperated the first part using MID (It's always 5 digits) but the second part has a varing length which then impacts on the third and fourth parts of the string.... Any ideas?
87261 WIMBLEDON 10:08 10:10
87169 NEWMALDEN PASS 10:13
87171 SURBITON PASS 10:15
87177 HMPTNCTJN PASS 10:16
To add to this I am using the POCKET PC version of Excel which does not have all functions so at the moment I am limited to which functions I can use (Can you add functions to the PPC?).
View 6 Replies
View Related
Apr 1, 2009
I have strings of data pumped out of a database like so "!OV !IPV ABL (850) !VL SM (150) !AD !PW !QT CC (-350)" If an exclamation point is listed, then no value follows however if no exclamation point is present, then each item will be followed by a value. I am trying to break this data out into a table. I am not sure if this is even possible. I am also attaching an example.
View 5 Replies
View Related
Apr 23, 2012
I am trying to extract the ounces (OZ) data from a string: example, BOX 15OZ 1819106287, CONTAINER 12.3OZ 1818176234. I need everything from OZ prior until there is a space.
I.e.
15OZ
12.3OZ
View 3 Replies
View Related
Jun 29, 2012
I have a file which contains one field showing all of the changes to our data. It shows the Field Name followed by a colon: then the Before value followed by a line | then the After value. If multiple fields changed then they are separated by a semicolon;.
I am interest in extracting the Before and After values for changes to the MP.COST field only. Here are examples of the data:
A1 = "MP.COST :4.00|3.50;MP.FLAG :Y|N;"
A2 = "MP.COST :4.25|4.12500;"
A3 = "MP.CODE :125064|200009;MP.COST :4.79|4.66000;"
For A1 I want to pull back 4.00 and 3.50
For A2 I want to pull back 4.25 and 4.12500
For A3 I want to pull back 4.79 and 4.66000
These are all of the Before and After values associated to MP.COST.
Kow I can accomplish this either through an excel formula or piece of code?
View 3 Replies
View Related
May 12, 2007
I have two variables 1st is a ETD (Estimated Time of Departure), ie 0510 which is string. 2nd is a KRT (Kitchen Ready Time), ie 03:00 which is date, time.
I need to put these two items in a cell as string, ie 0510 03:00
I tried .cells(rowindex, column) = me.etd & " " & me.krt
and I get 0510 0.1223243
then I tried
I tried .cells(rowindex, column) = me.etd & " " & format(me.krt,"hh:nn")
and I get compile error: expected function or varible
So how do I set the KRT to "hh:nn" and combine it with the string ETD to show 0510 03:00
View 9 Replies
View Related
Sep 9, 2008
I recently had to convert a text file to an Excel file. The text file had to be converted as delimited data since the fixed width column could not convert correctly.
Now that I have the data converted, I have several rows of data strings.
The data I have looks similar to the examples below:
41 AAITQ08082901PER0041 ABC v1.0 NES ABC P111 - Blue 7706 6547 Yes No 140 5 AAITQ08082901PER0005 ABC v1.0 NEG ABC Z113 - Silver R 9222 2743 Yes No 156123 My question is, how do I extract the numbers that follow the word "No" at the end of each string?
Assuming the data starts in column A, is there a formula that I could type into column B that would allow me to return the value of those specific numbers?
View 9 Replies
View Related
May 5, 2014
I have info displayed like this in cell c3
Bat 6Fm C6Hc 1K
Asc 8Gd C13yG1 198K
Chs 10GS C13yG3 34K
What I want is in cell J3 to return the first 3 letters and the numbers next to them three letters so in the example above it would return
Bat 6
Asc 8
Chs 10
View 13 Replies
View Related
Jul 16, 2014
I have an example sheet attached with the value I need manually typed in B and C.
What I would like is the formula to do this without me having too manually (as the full workbook has over 5,000 lines)
The ID will be between 1 and 4 digits and always in the same position
The name cane vary in length, and also be in a different position (depending on the length of the ID)
View 14 Replies
View Related
Feb 11, 2009
I have put a formula in excel to count how many times the word 'administration' appears in a column:
=COUNTIF(K2:K99,"Administration")
Unfortunately, the output that I am searching has mulitple words in it, separated with a colon and no space. My formula skips the count if the word Administration is not completely on it's own
e.g. Administration counts 1
Administration;Cardiology does not count
View 2 Replies
View Related
Sep 8, 2009
I have a few hundred rows of text in the fomat below: 1.23456 xxxxxxxxxxxxxxxxxxxx. The “x’s” represent text which is unique to each row. what the formula I need to extract the number (1.23456) at the start of the string? To complicate things the number may be reported to any number of decimal places, so the formula needs to be able to extract the first block of digits at the start of each row and report it as a number that can be used in calculations.
View 2 Replies
View Related
Nov 9, 2009
I want to extract data from array string and then sum the values. For reference attaching the excel.
View 14 Replies
View Related
May 28, 2013
how to get a validation list to a string. I know how to do it with a formula but I do not know how to do it with a validation list. I tried this and it errors out on the first line saying not a supported method:
VDL1 = Worksheets("new").Range("J3").Validation
Worksheets("Lookup").Range("L6") = _
Worksheets("Lookup").Range("Y1") + _
VDL1 + Worksheets("Lookup").Range("Z1")
View 5 Replies
View Related
Jan 13, 2009
I have a spreadsheet of location names that look like this:
UT-04560803-DF3
AZ-57611564-S32
etc...
I'm looking for a way to pull the last four digits out of the middle section of these entries, so that I end up with:
0803
1564
etc...
View 9 Replies
View Related
Sep 21, 2009
I am accessing a ratings system for horse racing and trying to extract the top-rated runners for each race using a database query. The problem is that every runner and rating is in one cell and separated by spaces. I have tried using text to columns but obviously can't use space as a delimiter as the horse names have spaces in them sometimes. The one cell basically contains the following string...
COCONUT MOON 100 CARIBBEAN CORAL 100 HOWARDS TIPPLE 97
A2 = COCONUT MOON
B2 = 100
A3 = CARIBBEAN CORAL
B3 = 100
A4 = HOWARDS TIPPLE
B4 = 97
So that I have each horse and it's rating alongside it in the adjoining cell, I figure I somehow need to use LEN, RIGHT, LEFT or something but can't think how to do this
View 9 Replies
View Related
May 24, 2006
I need to be able to take a string & remove all non numeric data. If I had "(123) 456-7890" I would want it to return "1234567890".
View 6 Replies
View Related
Jan 10, 2007
I have a sheet that I need to combine data from three cells into one and then get rid of original data.
Data to be combined:
A1=650
B1=1234567
C1=1998
D1=Desired Output
Desired Output:
A1=
B1=
C1=
D1=650-1234567-1XXX
View 4 Replies
View Related
Jul 1, 2013
I have a string like this:
VB:
test = "banana|apple|limon"
I did this:
VB:
test_2 = split(test,"|")
The code returned the test_2 var like a matrix with 3 data inside.
But when I try to copy the data inside with:
VB:
For i = 0 To UBound(teste_2)
test_3 = teste_2(i)
Next i
The code editor returns a ByRef error.
How to solve?
View 5 Replies
View Related
May 2, 2014
how to figure it out this lookup problem (lookup using partial string of match)...
View 5 Replies
View Related
Aug 4, 2014
I am trying to extract data from a text string by using formulas.
View 6 Replies
View Related
Dec 14, 2013
I have the data string below:
Career:25: 1-0-2 $13,765
I would like to extract the 1 between the : and - and as a seperate extraction would like te 2 between the - and the $ I have tried a few things but end up with the - as the length of the data changes
View 5 Replies
View Related
Mar 10, 2008
I'm currently working on a spreadsheet (see attached) which will have both numeric and text string data. This will be sorted by calender month and linked to another spreadsheet.
Could someone please list 2 functions for me.
The 1st would allow act like a filter allowing only data to be counted for a selected month from a drop down list.
The 2nd allows a counting of pre-set (drop down list) text strings to be counted.
Sorry seems a bit complicated and really I should have either a created a pivot report or used access but I'd like to continue with Excel for this exercise.
View 14 Replies
View Related
Apr 13, 2012
I have a worksheet with over 10,000 records. The column that lists where a person is willing to relocate can have up to 60 city/state entries in one cell.
Here is an example of what appears in one cell - this is exactly how it appears:
ASAI Los Angeles (XX , CA
DFO Pacific (XX ONLY), CA
DFO Pacific Area Analyst Laguna Niguel (XX ONLY), CA
SAI Los Angeles (XX ONLY), CA
Ldr Los Angeles El Segundo POD (XX ONLY), CA
Ldr Los Angeles Long Beach POD (XX ONLY), CA
Ldr Los Angeles POD (XX ONLY), CA
Senior Ldr (XXXX) Washington (XX ONLY), DC
What I need to do is be able to sort on city and state, so I wanted to be able to extract and separate the city and state. I tried using a find/replace (CTRL J) to enter a semicolon between each entry and thought I could do text to columns to separate, but that doesn't work.
How I could extract this information? Notice that the first entry is missing ) - that is throughout the records.
View 7 Replies
View Related
Aug 27, 2012
I am trying to paste data from one sheet to another using their string but not getting the logic.
below is the example for the same.
Sheet1 - data source
A
B
C
ABC
56.32
45.65
DEF
26.5
23.6
Sheet2 - Data needs to be pasted
A
B
C
DEF
23.65
36.65
XYZ
23.65
12.65
PQR
15.23
25.65
ABC
65.59
65.26
MNL
26.65
23.65
Value contains in Column B and C for ABC & DEF should replaced by the values in Sheet1
View 1 Replies
View Related
Nov 5, 2012
I'm trying to use application.match() but I'm running into a issue where I don't know if I'll be matching a string or long data type...
Here's the snippet of code:
Code:
' UNIT DATA
' Set Unit#
s1 = ActiveSheet.Pictures(Application.Caller).Name
iLen = Len(s1)
s1 = Mid(s1, 2, iLen - 2)
' Set Sel Ex Work Date
iCheck = Application.Match("MACHINE_NUMBER", Sheets("Allocation").Columns(1), 0)
[code]....
How can I get application.match to look for strings if s1 = "ABC123" or numbers if s1 = "123456"
I've tried dim variant and s1 + 0... But, haven't come up with a solution.
View 4 Replies
View Related
Mar 18, 2013
I am working with several userforms and try to transfer data obtained in one userform (lets call it Userform1) to another (Userform2).
Specifically, the idea is to have a commandboxA where the user can choose several options and the selection will define a string, and I want to use this string in Userform 2 to define a text.
Example:
The user chooses "solid" in the commandbox and I define the string as "rock"
Dim structure As String If commandboxA1.value = "solid" Then structure = "rock" etc...
And in Userform 2 I would like to combine the string structure with other strings, e.g.
If commandbox.value = "example" Then example text = "example text@ & structure & "text"
However this does not work, because the string which I have defined in Userform1 is not defined in Userform2. How would I define the string specifically, such that I have access to it in both userforms? How can I define the string as public? I tried several times, but could not get it to work.
View 3 Replies
View Related
Jan 8, 2008
How do you locate cells that have matching content within strings
Need to be able to match at least 5 consecutive characters within cells of both columns....
View 9 Replies
View Related
