Keywords As Delimiter To Split Cells
Jul 1, 2008
I've got a column of data containing addresses. What I want to do is split the column after certain keywords such as "street", "road", "close" etc and move any words that come after these in the cell into the next cell to the right.
For example, in one cell I might have "54 Burberry Road Finsbury London"... what I want to do is have part of the text remain in the original cell i.e. "54 Burberry Road" and the rest, "Finsbury London", moved to the cell on the right.
Not all cells in the column will match this format so if it doesn't find a keyword, nothing will occur and it will move to the next cell down etc. until it reaches the end of the column.
There are no delimiters (apart from the spaces) between the words.
I haven't figured out all of the keywords that I will need to search for yet but I should be able to extrapolate from something fairly generic.
View 5 Replies
ADVERTISEMENT
Oct 18, 2006
I recently got this task by my work employer to transfer information from several non-organized Excel-documents into one, complete and organized one.
The thing I have to organize right now is a column with thousands of zipcodes and zones in one single cell, only separated by spaces, like this:
Zipcode Zone
115 54 Gothenburg
443 67 Skane
What I want to do is instead of having to go through 24h of braindead control/paste is to use some easy and fast function or script that could pull out the last piece of string of information in every cell in that column into its own separate cell.
Although I am using Open Office Calc/Gnumeric on Ubuntu Dapper, if a script is necessary and only work with Excel on Windows/Mac, that is no big problem for me to solve. So please, any suggestion is welcome.
I will attatch two examples for you to look at, to get the whole picture.
View 8 Replies
View Related
Nov 11, 2008
I have a column with cells that look like: XYZ - JobABC - Area 1-A. I'd like to split the column into to and have it look like: XYZ <next column> Job ABC - Area 1-A
Normally I'd use the text to columns function and "-" as delimiter, however, there are other instances of "-" which I do not want separated. Note that the XYZ can vary in length and therefore fixed width will not work either. I have attached a small sample with the different variations that I could encounter.
View 7 Replies
View Related
Sep 3, 2008
I'm prepping this file to be pushed through mail merge. The sheet has 25,213 rows.
Column I contains a long string of character (for the purposes of my project the field is called Subjects) and $ used as delimiters (approx 70 or as few as 1). This column needs to be split apart by $ and the separate Subjects that result from splitting apart by $ needs to be placed on a separate line. Once each Subject is on a separate line, all of the Subjects need to be lined up in Column I, one under another (the obvious result at this point is GREATLY increasing the number of rows in the sheet). Next, all of the data that is contained in each Subject's originating row will be copied to each of newly created rows (without copying over the Subject contained in column I).
After writing this all out, I realize (once again).
View 14 Replies
View Related
Apr 24, 2012
How do i split some text in range if it does not have a delimiter? I would like to have a comma after each letter/number or have the option to split to individual cells
I know how to use the vba split function with a delimiter but cant figure this out.
I.e susanna
S,u,s,a,n,n,a.
View 4 Replies
View Related
Feb 28, 2007
I have one column in a worksheet that I want to split into 2 columns. If there is one "(" in the cell, I want to split at that point, but if there are two "(" 's, I want to split at the second occurrence. I can use the Like operator to identify the cells, but is there a way to specify which delimiter in a sequence of identical delimiters to split at? Here are templates (not actual data) of the two type of cells:
Name1 (ABC) (Name2, Name3)
Name4 (Name5, Name6)
For the first, I want to split on the second "(", before Name2, and for the second, I want to split on the first "(", before Name5.
View 4 Replies
View Related
Feb 14, 2014
I am looking for a solution to split text from numbers.I have found a couple examples on the web but I cannot get the examples to work with the correct syntax to function.
Sample cells. The string could be any integer or floating point number with text. (The text is always after the number.)
1234txt
12.222txv
102.1hknn
Result needed
col1
1234
12.222
102.1
col2
txt
tvx
hknn
View 4 Replies
View Related
Aug 24, 2012
My excel database sheet contains inventory of products and quantities, each is defined in the form of combined number followed by text without a delimiter. For example 2AWS means Two (2) of AWS.
Each cell of the inventory field may contain up to two products, each product is identified in the above format; but the pair of products are separated with a comma.
The inventory field is occupying from cell $A$2:$A$5000; and is defined as a named range "INV_QTY" with scope within the entire workbook. I need to calculate the total inventory for each product.
Here's an example:
Cell A2: 3GFG, 1AWS
Cell A3: 2NG, 2AWS
Cell A4: 3AWS
Cell A5: 4QLK, 4GFG
etc.
The desired output should be calculated as follows (assume Input will be on Column C & D)
Product - QTY
GFG - 7 (i.e. Cell C2: GFG , Cell D2:7 etc.)
AWS - 6
NG - 2
QLK - 4
View 7 Replies
View Related
Jul 3, 2014
I want to split the contents of a single cell(ALT Enter as delimiter) into multiple cells and retain the values in column B.
Ex:
A1: apple
banana
car
house
A2: yellow
B2: building
x
y
B3: O
Output:
sheet 2:
A1: apple B1: yellow
A2: banana B2: yellow
A3: car B3: yellow
A4: house B4: yellow
A5: building B5: O
A6: x B6: O
A7 B7: O
View 9 Replies
View Related
Dec 1, 2008
I have a string of text in one cell on Sheet 1 (ie., A1, Sheet 1), here is a excerpt:
A-dec International Inc., A. Bellotti, A. DEPPELER S.A., etc ...
What I need to do is split the cell into separate rows, using the comma as a delimiter. I will be reading the cell from another sheet and need a formula that will provide me with
A1: A-dec International Inc.
A2: A. Bellotti
A3: A. DEPPELER S.A.
View 9 Replies
View Related
Mar 7, 2008
I receive multiple worksheets containing transaction data with multiple numbers of row and columns. The data columns contain varying types of information like: price, quantity, location1, location2, loading dock, transaction date, buy/sell, purchaser, seller, currency unit, etc.
I have a need to concatenate into a single cell in a blank column, data from a number of cells in each row -- sometimes two cells, sometimes more. In some instances, I'd like to concatenate data for location1 and location2 with a delimiter like a semicolon-and-a-space to create a cell with: "location1; location2" Or in another instance, I'd like to concatenate multiple cells in a row, like: "location1; price; quantity; transaction date", etc.
I'm looking to create a macro that, with the cursor in a blank cell in a blank row, would open an input box, ask a user to select with the cursor the cells in the row to concatenate and then concatenate them into the blank cell in the row.
Ideally, the macro would populate the entire blank column with the concatenated data for the group of selected cells (from the same columns as in the row where the cells to be concatenated were selected) for each row where there is data. It is also possible that some cells in some rows of the selected columns would contain no data. In that case, I'd be looking to return just the delimiter, like: "location1; ; quantity; transaction date"
Right now, with no macro, I do it by hand like this:
To concatenate into cell I6, the contents of cells A6, B6, G6 and H6, I do a "=CONCATENATE(A6,"; ",B6,"; ",G6,"; ",H6)" in blank cell I6, but I'm looking for a way to do it fast in a macro that would fill in all of Column I.
View 5 Replies
View Related
Jul 23, 2012
Macro - it is currently set up to find a specific text, and activate that cell. From there, the spreadsheet has data in 6 cells in the 6 rows below the active cell. What I need the macro to do is to identify the product based on key words in a range of cells: that is, if any of those cells contain the string "total", than the product type is total, but if "total" is not found, look for keywords such as "alpha" and "beta", to identify the product type. If its not a total product, then i want it to look for "productB", "productc", and so on with relevant keywords until the product is identified.
I think IF(ISNUMBER(SEARCH can be used, but I'm not sure how, and I'm not sure how to search all 6 cells (to make things worse, the number of cells vary, so I'm using the "find" function to figure out the first and last cell that contain data).
View 5 Replies
View Related
Jan 15, 2014
I have a big file here : [URL] .....
As you can see, the second column contains abstracts which I want to use to determine categories for each document (each line).
Categories are as followed :
Diabetes prevention -> keywords : sugar, diabet*, insulin*
Obesity prevention -> keywords : weight-loss, fat*, LDL
etc. (5-8 categories)
To target one keyword, I found this formula :
=IF(IFERROR(SEARCH("*diabet*"; $B3); 0); "Diabetes prevention"; " ")
What I would like to know is how to add more than one keywords in this formula.
I would also like to know, if it is even possible, how to put every categories in this column, so that i don't have a category per column ... It's hard to explain.
I want to have in front of each line the corresponding column. It means that the formula has to search for every keywords and select one or more appropriate categories.
View 2 Replies
View Related
Nov 12, 2009
I have names that look like this:
SmithJohn and i want to seperate into two cells so it looks like
Cell 1: Smith
Cell 2: John
View 9 Replies
View Related
Jan 18, 2010
Is there an "easy" way (a formula) to split an amount into different cells (periodes) (see the example under)?:
Amount Periode Jan Feb Mar
900 USD 01.01.10-31.03.10 300 300 300
Formulas?
View 9 Replies
View Related
Jun 15, 2006
I have created a VBA which organizes data from duplicate accounts into a single row so that it is properly formatted for our email system. The one issue I am having trouble handling is cell overflow. Column B cannot end up with more than 5 pieces of information. Each piece of info is seperated by a comma. If there is overflow, the extra data needs to be moved to the following column.
With the example I have provided, you will see that after running the macro, cell B2 has 18 pieces of information. I need to expand the macro so B2 contains 5 pieces, C2 contains 5 pieces, D2 contains 5 pieces, etc.
View 6 Replies
View Related
Apr 16, 2008
My data looks like this:
Mindy Moore
Mace Kook
Cow Moon
These names are in Column A. I want to split the first and last name into Column A and Column B of my spreadsheet. I Usually copy row 1 to a txt file then import with "space" as my delimiter. I basically want an automated method of doing this.
View 3 Replies
View Related
Jun 3, 2009
I would like to do a text split for the name only in cells where contains the word "Name". For example: Name A&A Enterprize
Text split function for it is =MID(A1,FIND(" ",A1)+1,255)
so it only recognizes A&A Enterprize.
How would I add an IF, Then function (If the any cell contains the word Name, apply the text split function, for cells don't have the word Name, just leave it blank) if i have to do this for a large data file so i dont have to copy this function for every cell that contains the word Name.
View 8 Replies
View Related
Jun 17, 2009
I have a list of first names and surnames, the first name and surname are in one cell together but seperated by a commer.
For example in A1 would be Steve, Jones
What I want to do is have this split into two new cells. So in A2 I would have his first name and in A3 his surname. Formula to do this is what I cant get to work.
View 2 Replies
View Related
Apr 24, 2012
I would like to know if you can split 1 single cell into multiple cells in 1 column?
View 11 Replies
View Related
Jul 10, 2009
macro of the attached sheet.
Actaully i want a macro which are splits one cell data into deiffernt cells or rows.
View 9 Replies
View Related
Feb 13, 2012
I have data that I've converted from a different source, and the formatting doesn't always come out as I'd like it. For example, I have data that has two different values, and I need to split them into different columns. Is there an easy/quick way to do this?
15* 27016* 1324517* 2086518* 24943
View 5 Replies
View Related
Oct 25, 2012
I would like a vba script to split a string of cells that are in the ACTIVECOLUMN.
It would split at each to the next column across.
i.e. split
C:Program Files est est1 est2 est3
to
A1
B1
C1
D1
E1
C:Program Files
test
test1
test2
test3
This is not for column A though it would be for the ACTIVECOLUMN range.
There could be up to 13 sub folders ()
View 7 Replies
View Related
Mar 23, 2013
the question: i'd like to split a large cell in to smaller, fixed-size cells (columns) by a standard formula.
example: consider cell A1 = 'ABCDEFGHIJKLMNOPQRSTUVWXYZ' i'd like a formula that created B1 = 'ABCDE' C1 = 'FGHIJ' D1 = 'KLMNO', etc
I used =Left (A1, 5) and got A1 = 'ABCDE' easily enough. I'm stumped at coming up with a formula that provides the next 5, and the next 5, etc.
the setup: if you want to know why i really need to solve the question i'm asking rather than using the split cell function:
i have a large set of data from a vendor. ideally, this worksheet will be used for other vendors that submit data similarly.
the script i'm writing takes a specific format--which i can't logically hold the vendors to. so, my specific formatting is on one sheet, referencing the data from each vendor on another sheet.
there is one field of data from vendors that is large ( >4000 characters, typically). however, this field makes my SQL server puke. i need to break this field down in to chunks of 1000 characters, and then re-concatenate after it leaves SQL to its final destination.
because i want to use some script automation to reuse this project, i'm reluctant to just use the split cell function to break up the data. rather i'd like to use whatever data is provided, and referentially split it up in my pre-formatted data.
View 2 Replies
View Related
May 6, 2013
Other than using text to column feature, how to split values in a cell to multiple cells. For example; in cell A1 I have something like this:
text1
text2
text3
text4
There is no comma or seperation. What i want to do is:
B1: text1
C1: text2
D1: text3
E1: test4
View 2 Replies
View Related
Aug 11, 2013
Ihave data in column "B" like bellow & i want split that one cell data to different cells base on space. i mean after each space data should add to next cell ( from column "C" data can split). (formula or macro)
41981 (C-2313) TEX-120 3000 M - Poly Poly Core - Silver Falcon
41981 (C-2313) TEX-60 3000 M - Poly Poly Core - Silver Falcon
42684 (C-3899) TEX-120 3000 M - Poly Poly Core - Silver Falcon
View 1 Replies
View Related
Nov 8, 2013
I have many columns in my current spreadsheet but I have two columns that I want to split in to two project names.
Currently I have one column with: Project A/Project B And the second column with the total cost of Project A AND Project B combined
The thing is that I can easily just create a new line but that would mean all the data in the other Cells remain the same and the only two that will be different are the above two columns. I will be maintaining alot of rows so I was hoping I do not have to create two rows for every new item then to merge both rows in reach column every time I have to put in a new Item.
I am hoping to have for example the below
Name Comments Project Cost
-------------------------------------------------
Item 1 This item relates Project A $150
to upgrade of Project B $120
SAS system
----------------------------------------------------
Currently I have it as
Name Comments Project Cost
-------------------------------------------------
Item 1 This item relates Project A/B $270
to upgrade of
SAS system
---------------------------------------------------
View 2 Replies
View Related
Apr 30, 2014
Column A has data e.g. cat is fat xy>zzy, dog is random xyzzy, rat is a hoax.set xyz, frog is alon.23e xyz, bullseye.8 x.z
I have a look up table as below:
Col D
xy>zzy
xyz
x.z
(Note: the values in Col D do not have a set number of characters, so I cant use a Right/left Formula)
I need a lookup to look for the values in Col D within Col A and return the values in Col D. Is this possible?
All I really need is col D characters separated from the first word/words.
View 2 Replies
View Related
Jan 20, 2007
Is there a way to split a single cell in to two cells?
View 9 Replies
View Related
Feb 6, 2007
I have some data that is imported, it's about 25,000-30,000 characters and 2,300- 3,500 words, it all comes in as one cell, A1, if I do a text to column on it I will loose everything past column IV. Is there anyway a macro can take the data and put each word in a cell by its self starting in A2? The words a have a space between them.
View 9 Replies
View Related
