Split Cell With 2 Different Delimiters

May 1, 2008

I'm trying to split a cell with 2 different delimiters.
As example

A1 10'48"
B1 10
C1 48

I have about 10.000 entries like those. And i think a macro should be the most suitable for it.

But I'm not experienced enough to make it work.

View 3 Replies


Split Text Across Columns With Delimiters

Apr 17, 2008

I have a column full of text with most data separated by commas, except sometimes between the commas there is a string, marked by ' ' , which itself contains commas.

For example: 45,'im a string, look at me',67,43,5,'im another string, look at me',78

I try to make excel put all the data into columns, so 'im a string, look at me' will have its own column, instead of being split into two columns. I tried telling excel that the ' character marks strings, but it just removed the apostrophes and kept splitting any string that contained a comma. I tried to use a special delimiter,' but excel didn't allow me to. Does someone have a macro that will do the text to columns for me, or is this possible to do with the regular text to columns feature of excel?

View 2 Replies View Related

How To Split Text From Text String Into Separate Columns - No Delimiters

Apr 8, 2014

I have the cell data as below

How would I split into a new column the first part which is a date into a new column, then the country and the remainder into separate columns?

I still want the original data as I need to check that the splits worked well?

16.5.90 CH 1671/90-4
18.10.1991 CH 3056/91-1
24.07.92 ch 2341/92-2
30.7.92 ch 2395/92-3
18.11.92 Us 3533/92-5
26.5.93PCT 1577/93-0
9.8.93 CH 2363/93-8
17.8.93 CH 2445/93-0
25.1.94 ch 209/94-6 ; 8.12.94 ch 3714/94-1
8.4.94 ch 1047/94-0
22.4.94 ch 1255/94-7
18.11.1992 CH 3533/92-5
18.11.1992CH 3533/92-5

View 2 Replies View Related

Extracting Numbers From Cell Divided By Multiple Delimiters?

Jan 30, 2014

I would like to extract multiple numbers from a single cell. The cells contain -, /, and blank spaces are delimiters. An example is 4 - 3 1/4 and because of the way they use that number, it really means 4 inches and 3.25 fifths of an inch (3.25/5 inches) which is weird. I want to go through each cell and store the 4, 3, and 1/4 as variables so I can then manipulate to convert them to the proper inch value (I can perform the conversion code, I just need to know how to set those variables in the cell). And I would like it to automatically do it when a cell has been changed. formatting to maintain overall table width dimensions so if you know anything about that,

both cells are blank

4 - 3 1/4

cell value is enter in on the left

4 - 3 1/4
converted cell value appears on the left

View 2 Replies View Related

Concatenating With Delimiters

Sep 21, 2009

I have a sheet that is going to list usernames in a range of cells (e13:e19). Who gets listed there depends on another condition. So, all the cells could have a name, or only a couple of them could have a name. In another cell I have a formula that is to concatenate the results of who is in e13:e19 range, each name to have a comma between. The problem results when not all of the cells have a name .. the commas still appear ... so results could be ted, mary, bill,,,,bob, jeff ..etc.

I am using the formula below but it still allows the non-needed commas to appear:
=MID(IF(ISTEXT(E13),","&E13,"") & IF(ISTEXT(E14),","&E14,"") & IF(ISTEXT(E15),","&E15,"") & IF(ISTEXT(E16),","&E16,"") & IF(ISTEXT(E17),","&E17,"") & IF(ISTEXT(E18),","&E18,"") & IF(ISTEXT(E19),","&E19,""),2,2000)

View 3 Replies View Related

Concatenate And Delimiters Redux

Sep 21, 2009

It was being able to concatenate a list of names with commas between names. The resolution was: =Substitute(Trim(E13&" "&E14&" "&E15&" "&E16&" "&E17&" "&E18&" "&E19)," ",",")

This worked great until my boss said "I want first and last names. Now someone whose name makes the list is shown as john, smith.

View 5 Replies View Related

Macro To Join Two Columns With Delimiters?

Jan 14, 2014

I'm trying to write code to join two columns of data, and then join all of those into one cell.CA




I want to be able to run the macro and have it put {"CA":"California","DE":"Delaware","CT":"Connecticut"} into cell C1.

I was thinking that this would have to be a two part macro. First, it would take each specific row and join CA and California and add the colon, quotation marks, and the comma, and put it Column C. Then, after it's gone through every row, it would join every cell in column C together.

This is what I've written so far:

Sub test1()
Dim r As Range
Dim lr As Integer


Now I need to figure out the second part. How do I then take all of the join cells in column C and join them together, adding the curly braces at the beginning and the end, and removing the comma from the very last row?

View 4 Replies View Related

Macro To Import Log Files With Different Delimiters

Sep 29, 2009

I need to import a number of log files into a spreadsheet, each log will go into a separate tab. The logs are text files (.log) that have the same structure but slightly differ from each other.

For example all the logs will have many lines of data all starting with a date and then a number of delimited fields. In some logs the delimiter is a coma, in others is a unique charachter such as "{" or "@". Also the delimited fields are not a given quantity, they can vary too from log to log. How can I write a macro that understands what delimiter needs to be used and then imports all the file accordingly into one spreadsheet (in separate tabs)?

I tried something with the macro recorder and browsed for directions on the internet but really am clueless on this one. I am using windows 2000, and referenced Windows Scripting Runtime.

View 3 Replies View Related

Splitting Column Data Using Delimiters

Oct 3, 2011

Having some problems splitting data within a single column into several using VBA rather than a Formula. (I have been able to get working using a formula). I have found a few similar theads but nothing i seem to be able to convert with my some what limited skills

My data is always in a sheet called "Release Data" in column A, the number of rows varies daily. The data is always extracted with the delimiters in the same position E.G.

NNNNNN_AAA_Variable length txt

I would like to split the data into columns I, J and K.

View 2 Replies View Related

Import Text And Digits With Different Delimiters

Jan 9, 2008

I have a spreadsheet which performs certain operations as long as I insert 3 values in 3 cells. The results for different scenarios are shown within the same sheet. excample:

I insert
then many calculations are performed automatically and the result are shown in other cells in the same sheet.

My goal is to be able to copy/paste ca 8-10 strings of information at the same time. All of them at the same time, instead of only one at the time. Also, there will be a textstring and some digits I don’t need along with the copy/pasted info.Ex:

Name Time Date X1 X2 X3
Lou 22.11 01.03.2006 3,5 2,05 9,72
Lie 12.01 01.03.2006 2,5 2,65 1,22
Tom 17.09 01.03.2006 4,5 8,09 3,28

View 2 Replies View Related

VBA Code To Remove Larger Values Between Two Delimiters

Feb 27, 2014

I have a set of data in column A that looks like this:

White:0:0|Counter Height:0:0|Orange:40:0|Counter Height:40:0|Green:40:0|Counter Height:40:0|White:40:0|Bar Height:40:0|Orange:80:0|Bar Height:80:0|Green:80:0|Bar Height:80:0|

The delimiter "|" separates the unique values of an item (in this example they are dining stools of differing heights and colors), I need to only keep the lower value and remove the larger one.

The example above contains both White:0:0 and White:40:0 as two different values associated with the same dining stool. I need to only have White:0:0 remain and White:40:0 to go away.

View 2 Replies View Related

Sort Text Into Rows By Multiple Delimiters / Values?

Nov 19, 2013

I am trying to consolidate an .835 raw text file that is full of receipts and payments into rows by specific values/characters. Once opened within notepad/text format, I figured out that the ~CLP is the referencing value/character used to break up each individual payment.

I need to somehow use the ~CLP as the value(s) to sort the text into new rows, with each row pre-leading with the ~CLP.

For example:


New Row format needed:


View 6 Replies View Related

Save Excel File As TXT Or CSV With Delimiters Quotation Marks?

Aug 6, 2012

I need to "convert" Excel table to TXT or CSV file, but delimiters have to be ";" and text must be in quotation marks "".

View 1 Replies View Related

Separating Data With Multiple Delimiters Into Columns AND Rows

Apr 8, 2013

I have a column within a spreadsheet that has data separated by 2 delimiters, a "" and a "/". (This data has been extracted from a linux-based file.)

For example: "1100789/3200899/6xlm-sgt-455-0987"

The items of data are from a bill of material (parts explosion) and the number preceding the "" is a quantity and the numbers preceding the "/" is a sub-part number of the main part number that is entered into a column to the left of this data string. (unseen in the example)

I need to take this string of numbers and place the quantity in one column, the part number in another column, then add a row and continue to populate each column until the data has all been separated, then move on to the next main part number row to continue the process.

for example:
1 100789
3 200899
6 xlm-sgt-455-0987

Is this possible with the data tools in a spreadsheet, or will I need to write a macro?

View 9 Replies View Related

Split Data In A Cell And Get The Value For The Split Data From A Different Table

Jul 31, 2006

Here's my problem. I have a cell where there are many data strings seperated by ",". Each data string has a seperate value of its own like for e.g:
A2: aa,ab,ac

String Value
aa 1
ab 1
ac 3

What I want it accomplish is that, split the A2 cell into the different data string entities seperated by ",", then get the corresponding value of each of the data string entity, and to take the average of all the values of the different data string entities.

View 9 Replies View Related

Split 1st & Last Names & Split Addresses After 1st Comma

Mar 6, 2008

I'm using Excel 2000 and I have a spreadsheet with 4 columns (A-D) and many (500+) rows.

Part 1:
Colums A & B both contain identical data - a first name and a last name in the format "John Doe".

I want the second word ("Doe") removed from all cells in Column A so that only the first name remains, and I want the first word ("John") to be removed from every cell in Column B so that only the last name remains.

So, where A1 & B1 both started with the data "John Doe" now A1 contains only "John" and B1 contains only "Doe".

Part 2:
Column C contains addresses in the format:
"#5 - 123 Fake Street, Some City, CA 90210"

There is ALWAYS a comma and a space after the street address, then the name of the city or town followed by more data which may include one or more commas.

I would like everything BEFORE the first comma to remain in column C, and everything AFTER the first comma & space to be moved into Column D of the same row. The first comma and space are not needed again.

So, where C1 started with "#5 - 123 Fake Street, Some City, CA 90210", it now only contains "#5 - 123 Fake Street" and D1 now contains "Some City, CA 90210".

View 6 Replies View Related

Split Numbers And Split Words

May 8, 2009

How do I split numbers!

I have two problems/challenges!

Part I...
I got the answer 1987, and now I want Excel to take the numbers out and display...
1 in one box then i set + in the next, then 9 in the 3ed. box, next box +, then 8, then +, then 7 in the last so that i can have Excel make a SUM of it all to 25.

How do I split 1987 and put the numbers in different boxes?

Part II...
I want to make A=1 B=2... all the way up to 9, then start over again with J=1 K=2... up to 9 again and then over again.

So that if I write my name it comes out as a value of 14 (Odd = O=6 D=4 D=4 =14)

(AJSØ=1 BKTÅ=2 CLU=3 DMV=4 ENW=5 FOX=6 GPY=7 HQZ=8 IRÆ=9, It's the Norwegian alphabet, that's why there are some extra letters)

So how do I set up my Excel so that is ANY name is typed in I can get it out into a number from the values assign?

View 14 Replies View Related

Cell Getting Split Up

Feb 3, 2014

I have a very long HTML code that i am using for a description of something on our website, when i save the file in CSV format the HTML gets cut up and put into different cells on different lines ... the file needs to be saved in CSV format.

View 1 Replies View Related

Split Cell Into Two

Dec 27, 2011

I have a list of fixtures but each cell lists the teams in each game ... i.e.

CSKA Moscow v Real Madrid (in one cell)

List example....

CSKA Moscow v Real Madrid (Cell 1)
Leicester v Portsmouth
Leyton Orient v Charlton
Liverpool v Newcastle
Lyon v Apoel Nicosia
Man City v Liverpool

I want to split these cells so CSKA Moscow is in cell B1 and.... Real Madrid is in cell C1 etc.

View 5 Replies View Related

Split Cell Into 2

Sep 17, 2009

I have a column with info like

name - name

as in (column a): frank - john

I want to split that into 2 cells(columns) and drop the -, what is the best way ?

so now column b would have frank and c would have john.

View 9 Replies View Related

Split Up Contents Of Cell?

May 2, 2014

If Cell A1 Contained 1,154,546,487,47,36,48,47,668 and so on. Using a Formula or Macro how can I get it to take each number(s) before each comma and put it in the next column on the same row?

So using the above example numbers you would end up with 1 in column A1 and 154 in column B2 and so on and so forth.

View 8 Replies View Related

Split One Cell Value Into Rows?

May 8, 2014

I have got one column in which each cell has the complete data for the entire row.

I would like to split the data in one cell into the same row.

Got about 4000 records like it.

View 1 Replies View Related

Split Cell Format

Jun 11, 2007

I am looking for a method of combining 2 pieces of data into 1 cell

ie 2 Cells merged? and/or formatted? as one split cell as per the mock-up in the attached word document.

120 Enquiries, Closing Ratio of 40%

to look something like the attached

View 10 Replies View Related

Split Data From One Cell...

Mar 9, 2009

I'm working on a datasheet that needs some tweaks...

In column A, I have these data
CA 902-20
GA 100-10
UP 200-20
WC 100-20
City #123-89

how can I tidy the data into column B so that they have it will have a standard format of a single letter followed by (no space) 5 digits.
ex: C90220

I am thinking using Left and Right formula, does that sound right?

View 3 Replies View Related

Split Data Within A Cell?

May 29, 2009

My data within a cell has 3 values separated by commas i.e. abc, def, ghi. I want to separate these values into their own cells. Is there a formula or command that can do this? of course I can go into each cell and cut and paste the values onto another cell, but I have thousands of cells that need to be reformatted.

View 3 Replies View Related

Possible To Split Cell Diagonally?

Apr 3, 2013

I've searched on Google if it was possible to split a cell diagonally, so that I can enter a title on each side of it. One of the answers was from this forum but is very old, the thread was posted in 2005, and the replies are all turnarounds, like drawing manually a line, or such solutions. So I was wondering, maybe with all the new Microsoft Excel updates and upgrades, maybe there's a simpler more straightforward way to do it?

View 2 Replies View Related

Split Contents Of A Cell

Jan 30, 2009

i'm trying to do is split the contents of the cells in column A in to 2 seperate cells. The contents of the cells in column A are like this: alpha.bravo.charlie. I want to delete alpha and have bravo as the new contents of column A and charlie as the contents of column C.

View 2 Replies View Related

Split String In A Cell

Nov 13, 2009

I need to split a string in all cells in one column (delimiter = ""). What I basically want is to get just the first word in the cell.

View 4 Replies View Related

Split Text In A Cell?

Apr 17, 2013

I have grid references in a list I.E. TQ1234597865. i need to split that into 3 separate cells, so I end up with TQ, 12345 and 97865 in separate cells. Is there an easy way to do this with formula ?

View 2 Replies View Related

Split A Cell In Half

Jul 29, 2008

Is there any way I can split a cell in half, so I can put numbers in the left part of the cell, and the right part?

If so, I would I refer to those numbers in my macro?

View 9 Replies View Related

Copyrights 2005-15 www.BigResource.com, All rights reserved