Split Alphanumeric Strings Into Three Different Columns
Feb 27, 2014
I am working on a report but I need to split of text cells that contain text and numbers. I had worked in some formulas and I got stuck again when I drag down the formulas. In the attached file, you will see two tables. The one in the left is my current job. In the table of the right is how the table must look like.
The table is complete and will give you the whole idea.
Basically, I need to split the cell into three categories that are "Family", "Model" and "Phase". In example:
Cell Family Model Phase
CCA CCA
DC50X DC50X
DCX3300CRDDCX3300CRD
DPC2434 DPC2434
DCT-1700DCT1700
DCT-1800DCT1800
DCT-1800P3DCT1800P3
DCT-1800P4DCT1800P4
Attached File : Family Model.xlsx
View 4 Replies
ADVERTISEMENT
Feb 8, 2007
On a work sheet I have 20 columns that may or may not have data in it. In the 21 column I want to have a sum "of the values" in the other columns. Problem is the data is in this form i.e. 6 01/17/07 smr or 15 1/19/07 gtw or 24 01/21/07.
To clarify each cell will have a value of a number from 1 to 99 followed by 2 spaces. Then it can have 2 more spaces and an id tag or just finish with the date.
So, from the example above in column 21 I would have a current value of 45 and it could change if more values were added.
Note values are generated from a macro. It may be possible to change the macro so that a comma or such may be used to seperate the different "values / data types"
View 4 Replies
View Related
Oct 28, 2009
I have a column of one to three digit numbers (I5:I34), some of which contain an asterisk after them. In I35 i want a formula to find the highest value from that column. Additionally, if two numbers in the column are the same value, but one contains an asterisk after it, it should use that value in I35, instead of the one without the asterisk.
View 6 Replies
View Related
Mar 7, 2009
I have this formula that extracts numbers from alphanumeric strings.
{=1*MID(A1,MATCH(TRUE,ISNUMBER(1*MID(A1,ROW($1:$100),1)),0),COUNT(1*MID(A1,ROW($1:$100),1)))}
However this extracts only the 1st instance of the numbers
In a string like 123avfbsdf4556.. it'll extract only 123.
My questions are the following:
1. Is there a way that i could get the result as 1234556
2. A way which refers to a cell where I put in a number and it'll extract those many number instances. In the above example, if I put the number as 1, it'll extract 123. If I put the number as 2, it'll extract 4556 and so on.
I guess this would require some modifications to the Match function so that it does not look at only the 1st instance.
View 9 Replies
View Related
Jan 25, 2010
I have in two cells :
a1 b1
1m 1-3-6-11-17
how can I get:
1m1
1m3
1m6
1m11
1m17
using vba code.
View 8 Replies
View Related
Aug 29, 2006
I'm working on an attendance spreadsheet that shows the employee's absence using the format "X####", where X can be one of the following three values: S(for sick time), P(for personal time), and V(for vacation); "####" is the number of hours absent (can be from 0.25 to 8.0,measured in 15-minute increments, # can be either a digit from 0 to 9 or a decimal point, and there can be from 1 to 4 characters, but must be >0(I'm not worried about data validation yet).Examples are P8.0, V.5 (V0.5), S2.75,etc.
The spreadsheet is laid out such that each row represents an employee, and each column is a workday. After the employee's information are three columns labeled P,V, S. I'm trying to generate a formula that when applied to an employee's row, will display a running total of the absent hours based upon personal, vacation, or sick time taken. We'll call the range "CAL"(Short for calender)
To strip out the numeric characters on the right, I use the formula RIGHT(CAL, (LEN(CAL)-1)). To Determine which column the data should be summed in, I use the formula LEFT(CAL,1). Blank Cells are permitted, in fact most of the row will be blank. Now that I've figured out how to strip out the alpha from the numeric, how can I get the totals? I apologize for writing "War and Peace," but I wanted to be as concise as possible-
View 5 Replies
View Related
Nov 17, 2009
I need to sum the numeric portions of any cell containing a certain letter within a row. I found a solution that works if all the cells within my row are either blank or contain a string with the "desired letter" lets say the letter is "a" so that we can compare it to ....
View 6 Replies
View Related
Apr 6, 2008
I have a column with product packaging sizes with different syntaxes, such as:
"2 x 5 kg"
"200 l (212 kg)"
"1000 l (1400 kg)"
"5 kg"
"20 l (20,54 kg)"
As you can see the syntax varies a lot! Now I need a formula to extract certain numbers for different calculations. E.g. if the string has a letter "x" then the number(s) in front of it would be extracted. Or if the string has the letter "l" or "kg" the numbers in front of them (including decimals) would be extracted while discarding the rest. Is this even possible? Personally, I would change the whole system and break the strings across a number of columns but unfortunately it is not up to me...
View 4 Replies
View Related
Jan 29, 2010
I have a excel sheet where I have a list of strings seperated by a "-". I am looking to take the particular cell value and swap the characters before the dash with the characters after the dash. So for example:
FG03-FJ04 would become FJ04-FG03
AB02-CD12 would become CD12-AB02 and so forth....
I have tried findling around with the split function but cant seem to get anywhere, does anyone know a tried and tested method of doing this?
View 2 Replies
View Related
Jul 15, 2014
splitting out date/time strings to separate cells, but I cannot find how to split the following.
I have the serviceable date of an aircraft delivery set as 'S hh:mm dd-mmm-yy', which is due to being used by an external application in this format. This is entered by the user and I need to be able to extract the date time strings and switch them to dd-mmm-yy hh:mm, excluding the S from the result as a formula in another cell.
I keep getting a #value error or returning the whole string result.
View 3 Replies
View Related
Aug 16, 2008
Is there a way either by VBA or manually (preferably both, if possible) to actually unite the X amount of numbers that are in a cell given the contents is alphanumeric? I'll give you the following examples to see if you can understand what I' referring to?
DATA output should be
asd67,h876 --------> 67876
2,3,ujdj5&34 -------> 23534
909k86m34 --------> 9098634
View 14 Replies
View Related
Aug 15, 2008
I import data from another program in order to evaluate it. Unfortunately, one of the fields I need contains copyright data, however, it has been very inconsistently entered into the database. For example, sometimes the data appears "c1999." or "-1999" or "" or "[1999]" or even "19?" and also sometimes "1999, 1990" and many other variations on that. I discovered the link in the excel help file about extracting numbers from alphanumeric strings, but my situation is still too variable for it to apply; that file didn't take into account that alphanumeric strings don't always lump numbers and letters together. I was able to correct a few things, but my command of excel isn't knowledgeable enough to really come up with something effective.
Some ideas I had that I don't know how to implement: is there a way to strip non-numerical characters from an alphanumeric string? (I've been doing some find/replaces to get rid of some of it, but that is obviously not very efficient when I have to repeat this process daily.) Perhaps then I could just detect the first 4 numbers of the string somehow. However, that doesn't solve the problem of when a wild card is used as in "199?" or "20?" etc.
Bottom line, I just need to grab the first four numbers that appear in the string (but NOT additional numbers that occur after a wild card or a space if the year was not completed in 4 numbers; in that case I'd just be happy with a null value).
I've been doing this with a formula so far. My only experience with macros has been in simply recording them, not actually writing them, but I'll give anything a try.
View 9 Replies
View Related
Jan 5, 2010
I've got some time values in an Excel Sheet in the format hh:mm:ss. I need to split them into columns (including the colon) like below:
hh: | mm: | ss
I can do this manually using text to columns but when I use text to columns in my macro, it automatically changes the time format to h:mm:ss PM
View 2 Replies
View Related
Feb 16, 2005
I have two columns of data: 1 column can be possibly anything, either numeric, alphanumeric, or alpha only (VINS or vehicle numbers). The adjacent column are four different types of vehicles. I've tried concatentating the two to try and count the unique combinations (a truck maybe used more than once during the day), which I can get unique values, but not unique values for each type. Also, the range sizes are variable, but no more that 65 rows.
An example:
5CZ975 Van
25RW04 Straight
5CZ975 Van
ZJ6026 Van
So, the result I am after is 2 vans, 1 straight truck
I found an array in a another post, but it is not working properly (see cell F7 in the attached):
=sum(if(frequency(if($b$2:$b$18=$f7,$a$2:$a$18),$a$2:$a$18)<>"",1,0))
**ctrl+shift+enter**
View 5 Replies
View Related
Jun 16, 2006
Columns A & B contain several thousand Project Numbers (e.g., P1052, PA844, etc.). Many of the Project Numbers in column B are the same as in column A, but column B also has additional (i.e., newer) Project Numbers scattered throughout. Ideally, I would like to use a built-in function (versus a custom function if possible) that compares all the Project Numbers in both columns and then separately lists those that are unique to column B.
View 9 Replies
View Related
May 13, 2014
I have 2 columns i want to find out which items match in each column and put the matching value in column c. I have tried Vlookup and continue to get an N/A .. I tried countif and I get either an N/A or a value error. I have tried turning the cells into text but that is not working either..
column A has about 1700 rows and column B has about 4000
MOST CELLS ARE 6-7 VARIABLES.. satrting with either 01, 02, 03 with 4-5 letters following or have a 6 digit number or 6 letter value.
View 1 Replies
View Related
Jun 23, 2009
I need to automatically populate a column with an alphanumeric MFR0001 to MFR9999 and am currently using the formula below to add 1 to the start point (A1)...
A1=MFR0001
Formula in A2 =LEFT(C1,3)&(RIGHT(C1,4)+1)
This works perfectly if I start at MFR1000, but breaks and drops the 000 if I use it on the number I need to start with (MFR0001).
View 3 Replies
View Related
Feb 18, 2008
I am having to write a vba code for a survey data captured code online.
I am attaching the code and the vba module that is suupose to do
following things
2) dELETES THE LAST 3 COLUMNS
3) Initial 6 letters from each cell (Q0001=) UPTO (Q00011=)
4)Inserts new row at top from Q1 TO Q11
5) Sorts data on Q6 (Faculty/Staff)
Before all this the i need to insert blank cells for questions that have not been answered and shift the cells right (condition 1) ....
View 9 Replies
View Related
Jan 9, 2014
I need to get the count of a string from multiple columns. The problem is that string1 exists in column A and String2 can exist in one of eight columns. See the example below.
What I want to do is use two search criteria. I want to find the number of times I have Type=A and if the row has a case Beta. In the example above I should get an answer of 2. (From rows 3 and 9)
I tried countifs but I cant seem to get it working properly.
Attachment 288701
View 3 Replies
View Related
Mar 2, 2009
I'm new to this, and trying to get a spreadsheet organised for my nba fantasy team. To save time, (and me having to enter the positions of 300ish people individually) I'm trying to pull out their positions into seperate colums.
The data I am trying to pull is from these:
Jarrett Jack, Ind PG
Gerald Wallace, Cha SF, PF
=IF(FIND("PG",$B2),"PG","")
That is the formula I am trying to use, it finds the PG for some, but when it doesn't it gives me this "#VALUE!" in the cells, instead of a blank as I have tried to input.
Now, there are 5 combinations that I am trying to pull out: PG, SG, SF, PF and C.
There are multiple combinations that need to be pulled out, and placed into my 3 cells allocated (EG - 1 for Pg, 1 for SG, 1 for SF).
I have also found a flaw that takes the C from Cha (being the team) instead of from the position. Can I search for a individual C? I've tried to google these things, but I can't find an answer.
View 11 Replies
View Related
May 4, 2009
I have a spreadsheet that gathers information from multiple cells and strings the data into a long string - each piece of data broken by a comma in the string.
In another spreadsheet, I copy this data string into column A on sheet 1 - starting on row 2 and down.
On sheet 2 - and this is where I need help - I would like to have a macro that will take each data string on Sheet 1 column A and parse it out into multiple columns but the same row on Sheet 2.
ColumnA Row2 Sheet1 -> changing to many columns on Sheet2 but still on the same row.
The data string always represents the same number of data components - its just a matter of breaking it out and placing each data piece into its own column.
View 11 Replies
View Related
May 10, 2012
I have two columns. In the first column, there is in every cell some text. In the second column, there are some blank cells. It looks like this
textCOL1 textCOL2
text1COL1
text2COL1 text2COL2
text3COL1 text3COL2
text4COL1
text5COL1
text6COL1 text6COL2
How do I remove the rows which have text only in the first column?
The output should be:
textCOL1 textCOL2
text2COL1 text2COL2
text3COL1 text3COL2
text6COL1 text6COL2
View 4 Replies
View Related
Mar 13, 2014
As enclosed in my workbook,I want to separate the numbers between two strings of which one is in the left hand side and other is in mid.The data is in Column D.The simple way to understand is that,
Total Amount = Amount X Exchange Rate
Total Amount is in Column B, where as "Amount X Exchange Rate" is in combined form in column D.Yes, this I want to separate i.e Amount separately column and Exchange Rate in separate column.I have shown one expected result in column E,F.There are certain characters like ],= which you have to not to take any amount after ] or =.The currency is somewhere,$/$$/INR/IRS,etc.
View 4 Replies
View Related
Feb 1, 2009
i have in col A using 2007 these examples .
10. * 7x7 Showmed'cash (7) 3g b
5. * 08x90. Bean Rock (10) 6g b
8. * 31 Thenaia (12) 3f b
11. * Stormy Masika (9) 3g b
I have managed using text to cols separateting info inside the brackets and to the right .
Also the numbers very left .
My problem is there arnt any more things to use in text to cols to separate examples 7x7 Showmed'cash , 08x90. Bean Rock , etc .
View 9 Replies
View Related
Apr 2, 2007
Excel 2003
I have one column with a date and on the same row 12 other columns with nos
How can I convert this to 2 columns - first date and 2nd the no
View 9 Replies
View Related
Feb 10, 2014
I have a field that contains rows of data, each made up of a Number followed by Text. The length of the numbers differ - sometimes 4 digits, sometimes 5 or 6. Similarly, the text differs by word-count. An example is
2546 Nags Head
75698 Dog & Duck
634 Crown
I want a way of dividing the numbers from the text (numbers in one column and text in another).
Standard 'Text to Columns' won't work: I can't use 'Fixed Width' due to the number-length varying, and I can't use 'Delimited' and [space] as it will then split all the text up word-for-word (concatenating them back will take ages as my list is 480 rows long).
View 5 Replies
View Related
Jun 4, 2014
I am having quite a bit of a challenge here and am not able to code to split the text into columns. The text to columns does not work here unfortunately. Below is my situation. In one column that has the contract details I have the data as follows:
Account Manager Jennifer MacFarlane CONSULTING - GENERAL on 20-JUN-13 Function #:176749
Account Manager Janet Bewers CONSULTING - GENERAL on 25-JUL-13 Function #:176878
Account Manager Janet Bewers HEAT STRESS AWARENESS on 27-JUN-13 Function #:176828
Account Manager Janet Bewers TRACTOR SAFETY AWARENESS on 08-AUG-13 Function #:177383
What do I key in to get Account Manager in one column, the name of the person in another column and the one in caps in another column and the date in one column and the function in another column. I tried using left, right and LEN and something is terribly wrong with my logic
View 4 Replies
View Related
Dec 9, 2009
I need help to split text (shown below) into 3 columns, now all text is in one cell.
e.g.,
HIPP 1 PLUS PROBIOTIK 600 G MILCHNAHRUNG PULVER 1607000286
I have over 30,000 rows of data which contains different number of spaces within a cell. Please see attached file.
View 14 Replies
View Related
Dec 1, 2013
1 1100200157326 7000000000011126 Mr .AA BB CC 30,548.68 16 1,759.00 3,523.75
1
1100200157326
700000000001
1126
Mr. AA BB CC
30,548.68
16
1,759.00
3,523.75
2 1100200313905 700000000002 1127 Miss AA BB 48,329.53 9 1,969.00 3,944.75
2
1100200313905
700000000002
1127
Miss AA BB
48,329.53
9
1,969.00
3,944.75
3 1100200568628 700000000003 1127 Mrs. AA 24,990.00 5 1,825.56 3,652.84
3
1100200568628
700000000003
1127
Mrs. AA
24,990.00
5
1,825.56
3,652.84
I have data on column A and I want split data to many columns. This is Example.
View 5 Replies
View Related
Feb 22, 2010
I'm trying to split the address stored in column A into two columns (i.e. columns B & C). I got more than 30,000 addresses stored in column A and got the following excel function to do the job. However, it seems not to exactly solve my problem.
in column B : =LEFT(A1,FIND(" ",A1,20)-1)
in column C : =right(A1,len(A1)-FIND(" ",A1,21))
First, using the above functions, I got to manually drag the formula to the end of column containing 30,000 records! I tried to use vba to perform the job the script failed to do so. I know there has been something I missed in my vba script but do not know how to correct it.
for k = 1 to 30,000
Range("b" & k).value = LEFT(range("A" & k),FIND(" ",range("A" & k,20)-1))
Range("c" & k).value = RIGHT(range("A" & k),len(range("A" & k)-FIND(" ",range("A" & k,21))
next
What I really want to do is split the addresses in columns B & C with the first text ended with "Street" or "Road" stored in Column A and the other text after "Street" or "Road" in column C.
column A : 128 Johnway Road, 12/F, Flat C, Kowloon, Hong Kong
column B : 128 Johnway Road
Column C : 12/F, Flat C, Kowloon, Hong Kong
View 9 Replies
View Related
