Data - Text To Columns Operation

Feb 21, 2008

I have an issue with the Data - Text to Columns operation.

I paste some data into a sheet that is comma delimeted. I have a macro that runs the Data - Text to Columns operation and then moves the data to another sheet.(The data I need to paste is coming from a non-Excel application.)

I then copy a second set of data to another sheet and when I paste it, it automatically does the Data - Text to Columns operation but I do not want it to. I have seen this several times before and when I am manually manipulating the data it isn't a problem. When I use macros to manipulate the data, I don't get the desired results because my macro includes this operation.

I have done a little testing and this issue doesn't seem to always present itself. How can I paste the data without Excel trying to out-think me and just leave the data in the comma delimeted format it is in when I paste? What causes this? Its seems like Excel is seeing similar data and "assuming" I want to perform the same operation I just performed.

View 9 Replies


ADVERTISEMENT

Extracting Strings Of Text From Columns Of Data(text)

Apr 23, 2007

What I have is a column of data(text) which contains amongst all the text three strings of text in ever cell in the column which I require copying into three adjoining cells

The data I require is :-

(a) The persons name which is always after the word ‘Requester’ e.g. Requester Steve Robinson

(b) Their office location which is directly after the persons name and is in brackets e.g. (Newcastle User)

(c) The Approving persons name which is preceded by ‘Approved by’ e.g. Approved by Christine Hunting

See examples 1 & 2 below

Example 1

CR0/CRZ3651 Requestor Steve Robinson (Newcastle User) Tel: 01234 798157 Approved by Christine Hunting

Please install and configure 2 Ultra 2s (typhoon and lancaster) for use as ARTE workstations. These workstations require Solaris 2.5.1 plus the same patches as before

Example 2

CR0/CRZ3118 Requestor Doug Cunningham (Newport User) Tel: 0114 9881480 Approved by John Smithers

Please provide support to set up Cisco 2691 Router and PIX-506E Firewall to enable external connection of a remote terminal for project work.

As you will appreciate the text in the cells is of non standard lenght and the three pieces of information can be located virtually any where in the text

View 9 Replies View Related

Sheet1 With Two Columns Text Data - How To Get Data Automatically In Sheet2

Apr 14, 2014

I have two column (A and B) text data in worksheet1. If I type same text of column A (of worksheet1) in worksheet 2 column A, how can I retrieve data from worksheet 1 to 2 from same row.

View 1 Replies View Related

Algorithm For Addition (operation For Each ID)

Sep 25, 2009

Have an excel table with following data:
- ID
- number of bottles
- number of bottle crates (there are 20 bottles in one one bottle crate)

201688194000bottles
20168819200crates
2016883812000bottles
20168838600crates
201688396400bottles
20168839320crates
201688809000bottles
20168880600bottles
20168880480crates...................

I need to write a macro which will do this operation for each ID:

(bottles/20)-crates = x

and if "x" is not 0 then write down the value of "x".

There are two points I would like to point out:
- One ID may contain 3 or more rows (see 20168880)
- The macro will work with hundreds IDs so the algorithm should be fast (but it is not necessary)

View 3 Replies View Related

Copy And Rename Sheet In One Operation

Jun 14, 2014

Is it possible to copy sheet and rename the copied sheet in one operation .... have a hidden worksheet that needs to be copied and given a variable name dependent on the work sheets that are already present.

View 4 Replies View Related

Fill Each Hour Train Operation With Excel?

May 15, 2014

I have two tables excel: Table1 and Table2, as shown in the attachment below. I wish that: in Table 2 to be filled in each cell how many hours (eg. 0.166 hours) worked every train. There is an Excel function to look in Table 1, reading time intervals and automatically fill them in each cell of Table 2?

View 8 Replies View Related

Excel 2010 :: How To Create Automatic Operation

Jan 6, 2014

I am not too sure what this is called but I am using Excel 2010 and need particular document that I use repeatedly. Basically, if a particular cell in the document does not contain a phrase then I want Excel to automatically change the text in that cell to bold and red and 2 other cells in Excel to bold and red as well. For more information, the cell is 19–20 and K–O and the phrase in the cell is "No Inconsistency".

So if "No Inconsistency" is not found in that cell then it automatically changes the text in that cell to bold and red, and it also changes two other cells that are found in that document to bold and red as well. The coordinates of those two other cells are 19 with H–J, and the other one is 17 with H.

View 2 Replies View Related

Excel 2007 :: Divide Operation Executes More Than Once

Mar 12, 2012

Using Excel 2007.

My vba code seems to be dividing a range by 1M more than 1 time

My initial value is 51543942

After by code runs the display is 0.00 MB and the value in the formula bar is 0.000000000051543942 or 5.15439E-11

I would like the final display to be 51.54 MB

what I might be doing wrong?

Code:
'Format columns
r = .Cells(Rows.Count, 1).End(xlUp).Row
Set rng = .Range("C2:C" & r)
.Range("IV1").Value = 1000000
.Range("IV1").Copy

[code].....

View 8 Replies View Related

Error :: 'operation Is Not Allowed When The Object Is Closed

Jul 28, 2009

I keep getting an error in this code while trying to write the results of a query to a sheet in the current workbook. The error i get is 'operation is not allowed when the object is closed'... since i didnt close the recordset before trying to write the results to the worksheet, im at a loss to explain why im erroring...


Public Sub fillWorksheetWithData(dataSheet As Worksheet, conStr As String, sqlStr As String)
'clear datasheet
LastRow = dataSheet.Range("A65536").End(xlUp).Row
If (LastRow < 2) Then
LastRow = 2
End If
dataSheet.Range("A2:A" & LastRow).EntireRow.ClearContents

View 9 Replies View Related

Do Operation On All Sheets Except The First Sheet In The Book Every Time I Update It

Jun 5, 2007

I have a workbook that I would like the same operation on all sheets except the first sheet in the book every time I update it (which is once a week).

I get data (currency format) for all of my employees on a weekly basis. Unfortunately this data is shown as a negative number and I need to invert these values. (So I use the formula below)... but I have to do this manually for about 35 sheets and I figure I could automate this somehow with a macro. Here's what I do usually and what I would like the macro to do automatically:


I want to insert a column after column G.
I would like to enter the following formula into H2:

=IF(G4="u", "u", IF(G4

View 9 Replies View Related

Hyperlinks :: This Operation Has Been Canceled Due To Restrictions In Effect On This Computer

Mar 6, 2010

while trying to view a hyperlink i just created in a cell within a worksheet, a message popped up saying:

mircosoft office excell:

X this operation has been canceled due to restrictions in effect on this computer. Please contact your system administrator.

View 9 Replies View Related

Hide Workbook Opening/closing Operation On The Screen

Jul 6, 2007

I am running a script that populates about 70 or so workbooks based on information from the database. Naturally this script is quite slow but what seems to take the most time is opening and closing workbooks. Is there a way to "hide" the workbook opening/closing operation on the screen? I have tried using the

Application. ScreenUpdating = False

this sped the code up but you still see the workbooks opening on the screen.

View 2 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

Text To Columns With Data Downloaded From Website?

Jun 14, 2013

I have a problem with text to columns with data downloaded from our website. The address field is coming through with the whole address in a single cell with multiple rows. I have tried the following but all I get is the first row in the first colimn and the rest of the data:

Text to Columns, Delimited, Other and ALT-0010
Text to Columns, Delimited, Other and ALT-010
Text to Columns, Delimited, Other and ctrl-j

I have even created a macro to replace the hard returns with commas and then use comma as a delimiter but that still does the same.

I have attached a file with two of the records (1 tab for the original data and one with the commas)

Finally, I have tried this in both 2003 & 2010 and get the same issue in both.

View 3 Replies View Related

Move Data In Columns If Rows Contains Particular Text

Feb 24, 2014

If the row contains a text like ABC in multiple columns in a sheet it has to copy the whole column from that sheet and paste special into another sheet.

View 2 Replies View Related

Fitting Data (Text Vs Number) To Columns

Aug 17, 2009

I have a very big list in one column with the customers name and its account number. I am looking for a way to extract just the number from the cell.

I am aware of the 'fit data to columns' option but some of the customer names have an inconsistant number of spaces to assign a delimiter.

View 8 Replies View Related

Excel 2003 :: Perform Operation Variable Number Of Times?

Jan 29, 2014

I would like to perform a sum of randomly generated numbers between 0 and 6, but I want the number of independent random numbers to be dependent on another cell. I have =SUM(RANDBETWEEN(0,6) and then I get stuck. Is there a way to perform the RANDBETWEEN(0,6) a number of times stated in another cell? In this case that value is 67 but it will vary from around 5 to 150. I think there is a way to do this with macros, but I am not versed in visual basic. I am using Excel 2003.

View 2 Replies View Related

VBA Macro To Loop Text To Columns Through All Rows Of Data?

Apr 17, 2014

I am trying to get a macro to run in excel that takes a simple text to columns command in one line of data and runs the command on a loop through however many rows of data there happen to be.

I've attached two screenshots - one with what I've got now (Before.jpg) and what I'd like to have after the macro runs (After.jpg). The code below is what I used to get the first text to column breakout, which I can hopefully run on a loop to breakout anything in the DEPT column that contains a "/". It can ignore the rows that only have one department to begin with.

Selection.TextToColumns Destination:=Range("K2"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
:="/", FieldInfo:=Array(Array(1, 1), Array(2, 1)), TrailingMinusNumbers:=True

Eventually I'll want to create another macro that transposes the breakout text back into the DEPT column and populates it with the corresponding data, but I figure I'll take things one step at a time.

Attached Images
Before.jpg‎
ter.jpg‎

View 10 Replies View Related

Text To Columns Macro For Irregular Spacing Of Data

Mar 1, 2010

i have written a macro to parse data in to four columns using the text to columns fixed width option, but unfortunately the data i get changes its spacing and configuration every day, meaning that the fixedwidth columns dont separate the data correctly. below is how it looks some of the time.

46632hac5 Jpmcc 2007-ld12 a5 19,340,005 315
0738qac5 bscms 2007-pw17 a5 23,142,005 265
61746wcz5 msdwc 2000-prin a5 3,600,005 305
32108hp75 bacm 2007-2 a5 2,000,005 465
46630edf5 lbubs 2006-c1 a5 1,000,005 285...............

View 9 Replies View Related

VBA Macro To Process Data After Paste With Text To Columns..

Mar 24, 2009

My problem is that data from previous applications are "bleeding" into new applications while running my macro.

I am utilizing the macro below to paste an application and then to convert the application to upper case in addition to some additional formating changes. After performing the macro I save the newly created document to a different folder. With new applications I repeat the above steps.

I tried to incorporate the clearing of the clipboard in my macro using: Edit=>office clipboard=>clear all. The keys strokes during the " record macro" process do not seem to record in the macro.

I am using Excel 2003, SP2

Sub Process_Application()
'
' Process_Application Macro
' Macro recorded 3/23/2009
'

'
ActiveSheet.Paste
Columns("A:A").Select

View 9 Replies View Related

User To Select Top Left Of Data And Then Automatically Do Text To Columns

Feb 15, 2012

Data is input into my spreadsheet. It is not always in the same cell, and is not always the same length vertically. I want the user to select the top left of the data and then automatically do text to columns. Here is my code that is not working! :

Sub TextToCol2()
Dim rng As Range

Set rng = Application.InputBox(prompt:="Select a cell", Type:=8)
ActiveSheet.Range("rng:A" & Range("A65536").End(xlUp).Row).TextToColumns DataType:=xlDelimited, ConsecutiveDelimiter:=True, Space:=True
End Sub

View 3 Replies View Related

Excel 2007 :: Text Formatting With Data From Multiple Columns?

May 8, 2013

My data is in column A. I need to have the data as in column E and F. Column E is the field names.Rows can be up to anywhere and may very.

Excel 2007
A
B
C
D
E
F

View 8 Replies View Related

Count The # Of Rows In A Spreadsheet In Which There Is Non-blank Text Data In 2 Separate Columns

Dec 5, 2008

I am trying to count the # of rows in a spreadsheet in which there is non-blank text data in 2 separate columns. For instance, if the spreadsheet looked like the one below (dashes just for formatting purposes):

NAMES-------THIS-------------THAT
Mary-------some text--------some more text
John-------<blanks>---------just text here
Sue--------just some here-----<blanks>
Dave-------something--------something else
Adam------<blanks>-----------<blanks>

The total # of rows with something in both the "THIS" and "THAT" columns above would therefore be 2.

View 4 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

Loop: Macro To Go Through The Table And Preform An Operation In The Corresponding Cell In Column B Depending On The Value In Cell A

Mar 27, 2007

I have 2 columns and 10 rows (A1:B10). In column A are certain text values. How do I write a macro to go through the table and preform an operation in the corresponding cell in column B depending on the value in cell A? for example, if A3 = "Complete", overwrite the formula in B3 with the value (paste value)?

View 2 Replies View Related

Fundamental Operation ("Compile Error, Invalid Use Of Property.")

Mar 9, 2009

It seems so basic that I hate to ask, but I cannot figure out why this doesn't work. The subroutine is in ThisWorkbook. I keep getting: "Compile Error, Invalid use of Property."

View 3 Replies View Related

Excel 2013 :: Set One Columns Text To Color Based On Another Columns Results?

Apr 9, 2014

how to set one entire columns text to two different colors based on another columns values. So for example I have column A and B. Column A has two values called Internal and External. Column B is a title table so the entire column is just titles. We'll say it goes for 20 rows if you need a row count. What I am looking to do change the text in Column B to Red for External and Blue for Internal. I tried the conditional formatting and I just can't seem to find the right option.

I'm using Win 8.1, Office 2013.

View 4 Replies View Related

Calculate At All If Any Condition Within An "and" Operation Is Blank

Jan 18, 2007

How do I get a formula to not calculate at all if any condition within an "and" operation is blank?

View 4 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

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







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