I would like to create 384 by 2 table such as below. One column has series of alphabets (A-P) using 24 cells for each letter and other column has series of number (1-24) for each corresponding letter on left column.
A 1 A 2
I tried my best using my limited knowledge of VBA but macro fails by overwriting entire column in each loop (This code is for only one column, but I want to make two columns shown above).
Code: Sub alphabetNumber() Dim e As Long, f As Long For f = 1 To 16 For e = 4 To 387 Step 24 Range(Cells(e, 2), Cells(e + 23, 2)) = Chr(64 + f) Next e Next f End Sub
Hi, Column C has a bunch of addresses that have no spaces between the number and street. (27smith lane instead of 27 smith lane). Is there a way for me to quickly seperate the number and street name from the preceding number in each cell of column c? There are 1075 cells in column C.
Nine Hundred Sixty Three Thousand Seven Hundred Eighty One Eight Hundred Seventy Eight Thousand Eight Hundred Seventy Eight Eight Hundred Twenty Two Thousand Seven Hundred Eighty Four Eight Hundred Twenty Six Thousand One Hundred Eighty Nine Nine Hundred Three Thousand Nine Hundred Six
I have a large block of text containing hundreds of serial numbers that I want to extract from the text into their own cells. If I paste the text directly into excel then the whole lot goes into 1 cell. The serial numbers I need are individually wrapped in brackets so I wandered if it was possible to paste the block of text into excel using both '(' and ')' as value seperators.
I am copying data from a PDF. The data I need is in columns, spread over hundreds of sheets. I would like the data in the same layout (columns), but need it in excel instead of the PDF sheets.
I've tried copying and pasting into Excel, but the problem is that what is in columns on the PDF ends up as multiple rows beneath eachother, all in column A, on excel.
I've tried Text to columns, but that seems to only work if all the data you need to seperate is in the same row. What I need to ultimately appear in one row is currently displaying in three rows, one beneath the other. Is there a way for excel to automatically move what's in cell A2, for example, to B1 (and do this for all data, all the way down the sheet, that's similar to that currently in A2?
(If it would be useful for me to post a screenshot, could someone reply with how to do that in the most version of Excel?)
I have hundreds of file names, and to cut a long explanation short, they are exported as a CSV file to excel. In order indentify the owner of the file I need to rename the file with a commar where you see the dash or the underscore in order to have the name appear in the next cell, as can be seen with Syma and Kevin below, so I can then sort the columns etc etc.
I know there is a formula that I can use to achieve this, ie have syma.pdf and kevinc.pdf in the next cell.
I think it works on identifying how many characters along the name and then send i to the next cell.
as you can see below, there is no set amount of characters to put into a formula.
Could I say, for example, if after dash /underscore put next word in next cell?
I have a field that contains the following: 012100002030
I need a formula that will tell me whether or not the string contains a number higher than 2 or whether or not it contains more than one 2.
001000002011 111111000022 401110000000
the first loan would not meet the criteria as it contains no number higher than 2 and only contains one 2. the second loan would meet the criteria as it contains two 2's. the 3rd loan would meet the criteria as it contains a number higher than 2.
I have some data that is seperated by spaces. I need to split the data so each is in a seperate cell and then remove the spaces. I can do this if there is only one space inbetween the data, but that is not always the case. I attach a workbook with examples.
I am posting the worksheet so that it may be a little clearer. I have 2 worksheets, the input area and the model area.
In the input area, users will input their data with a maximum of 5 beverage items and 10 food items along with a certain number of other data inputs.
In the model area, i would like to seperate the food items from the beverage items along with their corresponding other information as you can see in the spreadsheet i posted.
However, food items can vary in number up to 10 as well as beverage up to 5. Also, users may enter the beverage and food items in any order they wish to.
Could you help me figure out a way to transfer my data from the input area to the model area and sorting the beverage and food items so that beverage and foods go into their specific zones? i will be performing calculations which are different from food and beverages.
I have a 25k line spreadsheet w/ 37 columns in use. The spreadsheet is organized by sales rep among others data. Is there a way i can insert some kind of command to auto seperate or copy all the data associataed w/ "rep1" into one tab, then "rep2" into another and so on?
I maintain a spreadsheet to track monthly sales of a few thousand items (see attached sample data). I'd like to have a formula that would sum only the last 12 months in the range of data. It would need to ignore all of the data before and the blank cells after the 12 months.
It's difficult to update the range each month for all of the products.
The table below is a portion of a calendar to track vacation and sick time. What I am trying to do is add all the hours of vacation ("V") and sick ("E") time that an employee has used in a cell at the end of each row which represents a month. So there are 31 cells that I need to look in and if it contains V... sum in the vacation cell. There may be a space after the letters and there may not be. They can also start with a decimal.
month 1 2 3 4 5 Vacation Sick
January E8 V8 V.5 V6.25 E .5 Sum of V (should be 14.25) Sum of E
I have not managed to find exactly what I am looking for so far..
I have cells with a mix of numbers and strings and I would like to extract certain numbers only.
I.E. 123a bcd8 acbd 1234e
I would like to only extract the first instance of numbers - so in example 1 - 123 and in example 2 - 1234. I have found various methods of extracting all numbers such as:
Function ExtractNums(r As String) As String 'Creates and returns a reference for a regular expression object With CreateObject("vbscript.regexp") 'Sets the pattern to be non-digits .Pattern = "D" 'We want to find all matching non-digits .Global = True 'Replace all non-digits with an empty string and return this value for the function ExtractNums = .Replace(r, "") End With End Function
I am trying to find numbers from a string. I have for example words "EUR Fwd 9x12" and "Eur Fwd 11x15" And i want to write a function that reads the first number from a string if there is only one number before "x" or two numbers if there are 2 numbers. So I have tried to build following function:
Function NumbersInString(Word As String) As Integer Dim i As Integer Dim FirstNumberInString As Integer, SecondNumberInString As Integer For i = 1 To Len(Word) If IsNumeric(Mid(Word, i, 1)) Then FirstNumberInString = Mid(Word, i, 1) If IsNumeric(Mid(Word, i + 1, 1)) = False Then Exit Function Else SecondNumberInString = Mid(Word, i + 1, 1) End If End If
Next NumbersInString = FirstNumberInString & SecondNumberInString End Function
What I would like to do is to divide a given chain of characters into the smallest chains which contain only letters and one number. To the examples given above, I'd like to receive the following sets:
xxxx1 yyy3 and yyyy1 xyzxx1 and yxz4 and xzx1 ... and so on
Is it somehow possible to do it with simple functions? Or is VB necessary (which sadly I don't know)?
In a data report i have a column which contains a mix of text and may contain an 8 digit number which could start in 0.
in column A i could have "Hello how are you 01237232 I am fine"
I wish to extract the number into a seperate column, and would ordinarilly use a mid or left/ right function, however the text infront and behind the number will vary in length, which means i cannot do this.
The number will always be 8 digits, could start in 0 but will not always, and it may not be present in all the cells in this column.
I have several lines with text strings containting three numbers in each line. I need a code to extract all three numbers from each text string. The numbers can be placed on the following columns in each row.
I'm looking for a way to extract the data after the first space in a string to after the first set of numbers (always 8 digits). The formula I'm using is not accurate since the number of words that appears between the first space to the first set of numbers changes.