I'm trying to automate a very laborious process where text data is taken from a series of cells in excel 97 and is turned into HTML tables - I've got it all working except for one thing, when I extract the contents of the text the paragraphs are lost and are merged into one long piece of text - obviously I need to insert <br /> characters where the paragraphs are to get this working. how I can extract text from with some kind of marker for the paragraph sections? Or is there a way to insert characters where the excel cell has a paragraph mark (ie a return key has been pressed).
I need a formula to insert a line break after every 42 characters in a cell , example if there is 266 characters in a cell there should be 6(266/42)line breaks.
=REPLACE(REPLACE(A1,42,0,CHAR(10)),84,0,CHAR(10))
I tried this but i am not sure how to get the start num to change to 126,168 and so on
I have a cell in excel that has 5 lines of text and I would like to count how many characters there are in each line of the cell. Is there a formula/macro to count them?
There are basically four fields of text (date, trans. #, merchant, $ amount) all in one cell that I would like to have in 4 separate columns without having to cut-paste, cut-paste, cut-paste. Is it possible for me to break this information up quickly into 4 separtate columns? I have about 400 lines of text like this and would like to find a faster way to do it.
I need to split in Excel some thousands of data rows containing text and numbers information of different character length Data to be extracted ( parsed) is stored in a single column( the first one) and needed results look like this:
Data to be extracted Needed results Address Name,Surname Phone no. Green Street no 16 Smart town Schmit Anders +3900098787867
[Code] .......
Data is composed by addresses, name of persons and phone numbers. Person's name is has two or three parts, address and phone numbers length is different ( 9 to 12 characters) I need to complete this difficult task (for me, obviously, ) in Excel using a combination of functions and/or multiple operations.
I am trying to develop a code which extracts the data from text files inside a folder (Folder test in my desktop) into one sheet. The Macro is in the workbook “Text Extract” which is an excel 2007 file. The data of each text should be copied to Sheet1 of this workbook one below the other. For testing purpose I have kept only one text file in the folder and was trying to copy the data from the text data extracted sheet to Cell A1 of Sheet1 of workbook “Text Extract”. The code works fine till copying the data, but shows below error in the line “Selection.Paste”:
Run time error 438: Object doesn’t support this property or method.
Below is the code: Sub LoopThroughFiles() Dim strFile As String Dim strPath As String
If I have 2 columns (A with data to be extracted, B for output). And 11,000 rows of data. How do I take information from column A and out to column B. For example, I want to identify characters in Column A, for example I want to identify "A" "#" and "SQ" to output. For example If column A, has "A" then Invitation, If column A has "#" then Business, if column A has "SQ" then square
I pasted in 1369 characters (including spaces) to a cell, and NO MATTER what I try, all characters will not print.
If I have the cell up for formatting on the function line, all text can be seen, but for some reason it cuts off the last sentance or more and will not show it in print preview.
I've tried all kinds of cell text formatting, cell merging, etc. with no luck. The only work around I found is to just have the "missing text" on the following row.
This is very similar to my previous post, which was solved. Now that I've extract the numbers, I need to extract the text for the specific work activities, for example 13Z or 9GGG. I'm assuming some variation on this formula:
Is there a function that can remove all text and other characters from cell and only keep the numbers? The numbers can be randomly in the cell so not only in the end or beginning.
This should be simple, but I am struggling with finding a way to use the search or find function to identify text characters. This is my situation, I have for example a cell that contains FW023 or D1234. I need to be able to count the number of characters that are text.
i would think I would be able to do it with the search or find function, but can't figure out how to get it to just count the number of text characters with in it.
I have some cells in a spreadsheet that contain unique numbers as the beginning of the cell (these numbers are always ten characters). In the rest of the cell there is text. the data would look something like this:-
SGGHNVT561 - 3yr Maintenance
I also have some cells which dont have the unique number at the begging, so the data would just look like:-
3yr Maintenance
In another table I have a list of the unique numbers and the name that they correspond to, for example (this is the 2 columns)
SGGHNVT561 - Dave HUKIDO8946 - Stuart HJUTIFHE78 - Graham
I have ben trying to construct a formula that only searches the beginning of the cells to see if it containts one of the unique numbers and if it does to put the name of the person beside the cell. If the unique number is not there to return a value like 'Check'.
i tried vlookup but then i ran out of ideas :/
this is what i came up with so far:- =IF(P7="","",VLOOKUP(P7,'[Master List.xls]Sheet1'!$B$2:$C$5,2))
The only problem i can see that this is looking for the full cell to be within the master list - but its really just the first 10 characters I want it to check
Is there a way to specify if there are 46 characters in a cell, to apply text wrap, indent the second line, and resize the row height to 25.5?
For example, I always will have text on merged cells B7:C7. I'd like to have a macro that determines if the text goes over C7 (I figured that it would take 46 characters to do this), that the merged cells will be text wrapped, then row 7 will be resized to 25.5.
I know how to record a macro that will text wrap and resize the row height but am not sure how to do the "if" condition.
I wish to limit the number of text characters in a cell and have excel prevent the entry of additional characters after limit is reached. I have tried the Data Validation but it does not preven the entry of additional characters. I want to be able to show the error immediately when the limit is reached and no additional characters are permitted.
I am trying to select and move text from one column to another. I have roughly 1,000 cells in column A and I would like to tell Excel to select characters at certain positions and move them into the adjacent column.
I want it to look like this...
column A column B ABCDEFGHI BCI
Each cell in column A has a 9-letter sequence. I would like to move characters 2,3 and 9 into the adjacent column
I'm faced with receiving data for time, in a text format, as follows:
Example of possible cell contents...
20secs 40m20secs 2h40m20secs
I'm not interested in the secs value but need to isolate the h (hours) and m (mins) values to use in another formula as numbers. The time could be shown in either of the above formats with either; (a) just secs, (b) mins & secs or (c) hrs, mins & secs... and of course the hours or mins values could be 1 or 2 digits in length. How can I determine (using formula only, not vba) what the values for hours and/or mins are dependent upon what is present?
I'm am trying to restrict the length of data that can be entered into a cell based on a drop down in that row. I have achieved this, but what I need to be able to do is use data validation to restrict the cell length to one of two values - so for example the length can only be 6 OR 9 characters long.
I cant see how to do this. At present the best I can do is rectrict to between 6 and 9 - but this would allow 7 and 8 which is not allowed.
I've scoured the internet for this answer and have not found a formula that works.
I have a list of domain names/URLs in column A of workbook 1:
jhjh.com hajfh.co.nz 123.123.12.12
I have a list of domain name extensions in workbook #2:
.com .co.nz .net
I want to determine if the cells in column A workbook one contain any of the strings in workbook 2 - note that I need to look at the string, not the entire cell in workbook one. If there is a match, true, if not, false for that cell within workbook one.
It's sort of a reverse array lookup with text. It seems that cross workbook and text is what causes the challenge.
If for any reason I completely close excel during the workday, then when I restart the application none of my macros are active. Even if I manually open the PERSONAL workbook that contains my macros. Any fixes other than restarting the computer?
I am entering large amounts of text into a text box uisng VBA code.
In VBE I need to line break the text using "space _" in order I can keep the text manageable on the screen. Problem is VBA is telling me there is a limit to the number of line breaks I can have in a sub, how I can solve this, ideally would like to paste a para of text at a time.
Overview: I have a workbook named MainPage.xls. This mainpage has several buttons and serves as a switchboard to launch other workbooks. Attached to this Main Page are two worksheets, "Source" and "Value1". The "Source" worksheet is graphical in nature and is used to collect data via a DDE channel to some production industrial processors and then on a timer "log" the data to cells in the "Value1" worksheet. Once the VB code is launched from the "Source" worksheet (via a start button), the code continues to run until it is stopped (via a stop button). The data is then graphed and printed out, etc.
This works very nicely and provides "live" graphing of some constantly changing data. Once the "Source" worksheet data VB code is running, I need to be able to minimize the worksheets and return to my Main Page and further be able to open any one of the other workbooks on the Main Page and work with that page(s). Thus far, I can minimize the "Source" and "Value1" worksheets and return to the Main Page and the VB code runs along nicely in the background.
However... IF I attempt to open any other workbook from the Main Page, my data collection/graphing code stops and I get a Run-time error 9, subscript out of range... and Excel completely shuts down.
I know this must have something to do with the worksheet that runs the VB code losing focus when another workbook is opened.
I have a workbook that prompts a user with a msgbox and then a SaveAs dialog. It works but when users intend to open the workbook from Internet Explorer, the SaveAs dialog stays behind the browser and everything freezes. Ideally I would want the workbook SaveAs to stay on top till user saves the workbook. Any help?. Here is my Open and Save code.
Private Sub Workbook_Open()
'Unhide Function for Macro protection UnhideSheets
