Text To Columns :: Macro To Split Text

Jun 17, 2008

I am trying to write a micro code to split text which is copied into cell A1 into columns. I can do this fine by going to "data" the "text to Columns" and selecting the places i want to split the text (this is the same for every piece of data i copy in).

The macro works perfectly every time. the problem is that the spreadsheet is shared and i want to protect certain cells on the sheet, when i protect the sheet the recorded macro does not work as the "data", "text to columns" is not available in a protected workbook.

I was just wondering if someone could help me, so i can run a macro to split the text which also allows me to protect cells. In the "text to column" option the "fixed width" (column breaks) i choose are: 4, 25, 34 and 43.

View 11 Replies


ADVERTISEMENT

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.94ch209/94-6;8.12.94ch3714/94-1
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

Split Text Into Columns?

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

Split Text Across Columns ..

Nov 15, 2006

Need to split the WORD into Col B and put the DEFINITION into Col C.

Here's an example of what's sitting in A1:

Title – A description of record contents

I've tried using the text to columns but can't get it to work.
(since the Words are all different lengths, something gets chopped off)

What I have today:
In column A (within a single cell is both the Word & it's Definition).

I need to extract the word ONLY into a new column (B)
and extract all the other words into column (C) (without the dash)...

I've also tried :
=LEFT(A1, FIND(" ",A1)-1)
and successfully stripped the Word into column B
but can't find any functions to extract the rest properly into C

I JUST noticed, some WORDS are multuple...example:
Information Protection Level – Used to identify information protection values per Pro 2227

Can you provide a function for doing a 3 word extraction to Col B?

I guess, what I REALLY need is for it to take "everything up to the dash" and put in column B......then put everything after the dash and put in col C.

View 9 Replies View Related

Text To Columns/time Formatting Split Them Into Columns

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

Split Text To Columns Over 2 Sheets

Sep 4, 2009

I have data in Column A that is from a text file that contains 50,000 rows , this is pipe delimited data that is 300 columns wide. I would like to be able to keep the columns but using the Text to Column functions means that I lose some them.

I was wondering if anyone knew how to separate the text in to 2 sheets, with the first 200 columns in Sheet 1 and the remaining in Sheet 2. The reason I would like to separate the information in to cells is so that I can investigate the data better.

Its not possible to traspose this information as the rows are nearly 50,000 long.

Here is a small sample of what 1 row is like, it not the entire row. Each "|" character represents a break and a new column.

PHP
A|01234567/163|01234567/163|AB123456B|Mr|Gordon|Brown|01-01-1960|1|10 Downing Street|SW1A 1AP|4|||||||||||||||||||||0|0|16-06-2009|16-06-2009|| 

View 9 Replies View Related

Split Text Among Columns By Using Functions

Feb 5, 2010

Once again I return to the brilliant ones on this board. I read the Excel help page for "Split text among columns by using functions". But my parsing task is more advanced than what I could gather from this function.

Here is the contents of cell A1 to be parsed:
Pack type,(make selection),Pack A[=4.95],Pack B[=5.95],Pack C[=7.95]

I need to extract 4.95 into cell A2, 5.95 into A3 and 7.95 into A4. How
Oh also, I have many variations of that example, and want your solution to work for the variations. So here is another actual cell that I have to be parsed:
Qty. discount,(Make Selection),1[=18.95],2 to 4[=17.95],5+[=16.95]

So, each extracted value will always be preceded by = and followed by ]

View 9 Replies View Related

Split Text Across Columns Formula

Dec 9, 2006

I got a question regarding a what formula can i use to act like the Split method in programming. I would like if this can be done with a formula not programming cos i am a programmer Basically i got a column full of data in the following format:.dddd.ddddddd.ddddd. Now the number of the "d" can be random in between the dots. So i would like to be able to split the line by looking at the "." .There is one thing though that each line does have 3 or 4 "." characters.

View 3 Replies View Related

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

Split Text Into Fixed Length Columns

Jul 20, 2012

I have 5 columns with data in each

I want to create a 6th column that looks to the columns on the left with data in ti and concatenates all data in the 5 columns and puts it into one cell in the 6th column however put a space between each break of data so that it can be distinguished which bit of data was in what column previously.

The challenge is the new 6th column can only contain 30 characters - When it exceeds 30 characters then create a 7th column and put the rest of data in the 7th column, again the 7th column can only have 30 characters so if exceeds this then put the remaining characters in a 8th column

There will never be more than a total of 90 characters in the original 5 columns so there will only need to be scope for a maximum of 3 additional columns

So for example

Column A had two words in it that totaled 20 characters (the space between the two words is also counted as a character)
Column B had two words in it that totaled 20 characters (the space between the two words is also counted as a character)
Column C had a word that contained 10 characters
Column D had a word that contained 5 characters
Column E had a word that contained 10 characters

Then the result would be

Column F would only have the data originally held in Column A (because it can't include Column B's data as this would exceed the 30 characters)
Column G would have data that was originally held in column B and column C - with a space between B and C data
Column H would have data that was originally held C, D and E - with a space between C, D and E data

Another point to consider is if in one of the orginal 5 columns had say 3 words in it and lets say the 3rd word is the word that exceeds the 30 character limit, then the whole of the third word is to be carried oved to the next new column, I can't have words cut in hlaf with one half in Column 'F' and the other half in Column 'H' for example.

View 1 Replies View Related

Split Data Into 2 Cells, Text To Columns

Feb 26, 2009

i have cells with city and state in them and i'm trying to separate the one column into two columns. the problem is, i'm trying to keep the city name in one column and the state in the other. some of my cells have two word cities like new albany, or upper arlington. the text to columns feature is separating those cells into 3 columns not 2. is there a way to do this?

example:
worthington, oh
upper arlington, oh

text to columns splitting upper arlington into 3 different cells because the only delimiter in the cell is a space. i need to keep upper arlington in one cell and oh in another.

View 7 Replies View Related

Excel 2003 :: Split Text Into Columns

Mar 29, 2012

I have a large number of product descriptions of varying lengths (column A) which I need to split into a maximum of 3 columns depending on the total length of the description. Each description in column A is less than 90 characters. Each column (B, C & D) can only be a maximum of 30 characters including spaces and commas etc. Also words cannot be split. Below is a sample of the result I would expect with the description in column A and the 3 extracted columns in B, C and D. I am using Excel 2003.

View 4 Replies View Related

Split Text Across Columns By Random Number

Feb 6, 2008

I am trying to split a cell into column by number. e.g. KOPAS LIMITED 30 ST CLAIR AVE W SUITE 1111 TORONTO ON
to

KOPAS LIMITED(new column)30 ST CLAIR AVE W SUITE(new column)1111 TORONTO ON. The numbers are random. My list is of 27000 rows long.

View 7 Replies View Related

Text To Columns - Only Want To Split SPECIFIC NUMBER Of Times

Jan 26, 2006

I am trying to split names across columns. The problem is that some names
spilt into 3 columns (first, middle, last), and others split across 5 or 6
(extra names, etc.)

Is there a way to specify split, using SPACE as a delimiter, but only split
on the first TWO spaces, then leave the rest alone?

View 9 Replies View Related

Split Address Text String Into Separate Columns

Mar 13, 2009

I would like to "reverse concatenate" an address text string as follows: ....

View 9 Replies View Related

Split Text Across Columns (apartment Number & Address)

Jun 30, 2008

I have a 2000 row sheet with an address field that contains the apartment number and the address. The text is displayed as unit number, then hyphen and the building/street number for example '101-1234 15th Street'. I'm looking to cut the building number from the cell and paste it into a seperate column. Ideally removing the hyphen completely, so that I have two columns 'unit number' and 'building/street address'.

View 5 Replies View Related

Split Comma Separated Text And Insert Into Next Row Retaining All Other Columns

Oct 26, 2013

Original table:
Column1
Column2
Column3
Column4

AAA
DDD
A1
X123, Y123, Z123

[Code] .......

View 1 Replies View Related

Import A Text File From A Specific Folder Into A Sheet Without Split It To Columns

Sep 26, 2007

I have a variable list. Each column will be 250 digits and numbers of rows will be variable. I want a macro to import a text file from a specific folder into a sheet without split it to columns. So we will work only in column A

Then macro will find B1002 wording in A1. if it is exist, it will copy the next 36 digits after B1002 wording if not then it will search A2 row. The next step will be to search and find another wording "B1001" if it finds it will replace copied 36 digits text. If it can not find B1001 wording it will go to upper row and search B1001 wording here and paste the text. This will go on till row shows #END. This means it reached the end of the list. And then macro will save this file as text file to another folder.

So macro will go to beginning to open other file in the folder and this will go on till last file in the folder.

This is the logic of the macro. Here is the sample of what I want.

Original Data: ....

View 9 Replies View Related

Find Text And Split Macro?

Dec 2, 2013

I want to split numbers in a cell. At the end of my report there is a text like this ;

Critical Path : 35-36-37-38-39-40-32-9-10-11-12-13-14-15 ;
Total Pressure Loss : 217.1

Pa text row changes in every report but it is always in a1 column is there any way to search critical path and split numbers in text.

My result should be : -35-36-37-38-39-40-32-9-10-11-12-13-14-15-

View 3 Replies View Related

How To Split Macro Enabled Text Into Different Cell

Apr 12, 2014

I usually copy ready macro from google. And I currently working on a check printing excel for my company to print check. I have googled and extract this macro from an author and place into my excel. But i run into a problem which is, the returned text will exceed the boundaries,

is there a way to spilt the text, lets say from after "dollars " to another appointed cell?

[Code].....

View 5 Replies View Related

Excel 2010 :: VBA Macro To Split Text In Every Cell Of The Same Column?

Apr 16, 2014

Excel VBA 2010.

I have this problem:
e.g Cells ""
C2 - 128.50 g
C3 - 211.01 g
C4 - 198.50 g
C5 - 179.34 g

I need to split the text into
Cell "C2" = 128.50 and Cell "D2" = g
Cell "C3" = 211.01 and Cell "D2" = g
and so forth

I wanted to avoid using delimited method with space and induce an automated method to format every cell of Column "C" one by one.

View 6 Replies View Related

Split Text File Into Smaller Text Files

Mar 15, 2005

I'm trying to take a large text file and break into smaller text size files. I want to open the data file, and parse into 5000 line smaller files.

Here is what I have based off of some MS KB
Sub LargeFileImport()

'Dimension Variables
Dim ResultStr As String
Dim FileName As String
Dim FileNum As Integer
Dim Counter As Double
Dim CounterMax As Double
Dim sPath As String
Dim FileCounter As Double
'Output File

View 9 Replies View Related

Text File Import With Text To Columns Splitting Same Text Differently

Nov 21, 2007

I have a macro which imports data from a mainframe dump text file and performs 'Text to Columns' on the imported data so that formula in the spreadsheet can act on the data. The code works perfectly well when I use it, but if a different user logs on and performs exactly the same mainframe dump and import macro the Text to Columns action splits the raw data in a different way and the result is that the split renders the formulae useless.

I've experimented a little and for some reason it appears that the 'Field Info' parameters which are produced when the Text to Columns function is recorded in a macro differ between users even though the raw data is exactly the same.

FieldInfo:= _
Array(Array(0, 1), Array(18, 1), Array(35, 1), Array(56, 1), Array(70, 1), Array(88, 1), _
Array(102, 1))

View 6 Replies View Related

Text To Columns: Split Data In Seperate Seperate Coloumn

Jul 23, 2009

Attached file where i m not able to split data in seperate seperate coloumn

View 3 Replies View Related

Text To Columns Macro

Sep 12, 2007

First of all i have to mention that i'm not a coder guy so excuse me if i'm no good in explaining the problem.

What i'm trying is to convert texts into columns. I constantly have an email containing short codes about my business and each code has a special meaning. So i need to convert these codes and lookup in my database. Here is an example of the source file in email.

V MFACTBEL 04-TLV * FH 42T B * USAFEP * FH42T440
V * MEDHCM EPAH342 EUDRPA1 * UDFSUB * EU5SCR
V FROCALC * TYPE-FH * V2514 * ULIVING * SOUNDPLU
V * AIRFLPAC * EC-REG * L405A71 * R690A71 * LNGTUR
V * MIRCOMF * CBL2-BA3 * 1DAYEC * TUR-MSP LFUEL
V RFUEL * WL-ST * UAXLE * TEXTILE * TTRCON77
V * ESH-LEFT * UDFP * 4*2 * TRACTOR CONC-BAS............

View 9 Replies View Related

Text-to-Columns Macro

Jan 5, 2010

I am using the TexttoColumns macro to split cell contents into two columns. It works well except for one condition where the data it is parsing has a trailing zero. Excel drops the zero but I need it in the output.

Example Sun Management Center Agent: 4.0

Result
Column A Sun Management Center Agent
Column B 4

View 9 Replies View Related

Macro To Compare The Columns Text

Apr 1, 2009

macro to compare 4 columns of text (first and last names) then add a checkmark in separate columns if they match and don't match?
For example I have this kind of data to compare and the results required:

Sheet 1
Last Name First Name
Smith Mike
Johnson Bruce
Hendrick Fred
Shaffer Kerry

Sheet 2
Last Name First Name Match No Match
Klee Pierre X
Verge Kerry X
Smith Mike X
Wright David X
Hendrick Fred X

I need the macro to mark an X in the Match column if the first and last names match only and if they do not match exactly a X in the No Match column.

View 3 Replies View Related

Text To Columns Not Working In Macro

Nov 25, 2011

I've got a report that has a period date in it and its in the format "ARP-12", which is not set as a date. If I highlight the column and click Text to Columns it puts it in a date format which I can use.

I recorded myself doing this to insert into a marco but the date format is not correct. When I do it manually ARP-12 comes out as 01/04/2012 which is what I need but when i run the code i recorded it comes out as 12/04/2011.

I've pasted the code I've got below:

Columns("F:F").Select
Selection.TextToColumns Destination:=Range("F:F"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
Semicolon:=False, Comma:=True, Space:=False, Other:=False, FieldInfo _
:=Array(1, 1), TrailingMinusNumbers:=True
Range("A1").Select

View 7 Replies View Related

Macro For Text To Columns Function

Mar 31, 2013

Macro for text to columns function (if this is the best way to go).

Our work report exports customer subscriptions which we call "role name". Our customers subscriptions have a valid and start date, but our customers also can purchase multiple subscriptions and we need to separate this data in order to report by subscription.

Our export has an * after each subscription, the date is in square brackets and each subscription is separated by a semi column.

So for arguments sake, let's say we have the following subscriptions:

subscription a
subscription B
subscription C
subscription D

if one customer has purchased four subscriptions on various dates, their exports may look like this:

Subscription A* [01/02/2012 12:00:00 AM]* [01/02/2013 12:00:00AM]; Subscription B* [01/03/2012 12:00:00 AM]* [01/03/2013 12:00:00AM]; Subscription C* [01/04/2012 12:00:00 AM]* [01/04/2013 12:00:00AM]; Subscription D* [01/05/2012 12:00:00 AM]* [01/05/2013 12:00:00AM]

I then use a text to columns function to separate by the * and the;
I then am left with a column for the role, one for the start date and one for the expiry date.

The roles are in alphabetical order, but sometimes some manual sorting needs to be done to delete the roles I don’t need and keep the ones I do. For example someone could have subscribed to subscription B and not in A that means that the first column may not have all the roles I need, it may be in the second or third column depending on what other subscriptions they have.

So not sure if text to column is even the best way to go?

View 6 Replies View Related

Compare Text Strings In Two Columns And Return Text From Adjacent Cell

Feb 28, 2013

how to Chk the text string in particular cell, compare it with a super set column and get the full from of the text string from another corresponsing column and the output will be corresponsing full form of the chked text string?

View 6 Replies View Related







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