Free Microsoft Excel 2013
Quick Reference
Free Microsoft 2013 Quick Reference Guide

Free Microsoft Excel 2013 Quick Reference

Text to Columns when more than one sentence in cell.

I'm trying to write/record a macro that will parse out the data in a cell. The problem is that there is more than one string of data in each cell (see example below). I.e. multiple sentences. So when I record the text to columns process using "space" as a delimiter, it is only parsing out the data in the first sentence and stops. The sentences starting with "Can somebody" do not get parsed out.

Example
I have tried several times to submit a live-chat query, however to my surprise my chat is never answered. I am not sure what is happening.
Can somebody please advice if I have any issue with my profile as nobody answers the query?

Or if there is a valid reason, please provide it so that I am aware of the issue. I do not want to waste my time on an automated system as I hardly see anyone replying to my query.

Can someone please point me in the right direction? Any help you can provide would be greatly appreciated.

Thanks
firstshot


Post your answer or comment

comments powered by Disqus
Hi
Is there a way to change this formula to show me when more than one row meets the criteria?

=INDEX('Sheet1'!$H$1:$H$48879,MATCH(1,('Sheet2'!A1='Sheet1'!$C$1:$C$48879)*(ABS('Sheet2'!S12364-'Sheet1'!$S$1:$S$48879)

Hi, I'm trying to create a program that will search for a word in a cell and return a 1 if the word is present, and a 2 if the word is not. That part is fine. I'm encountering trouble trying to search for more than one word in order to account for possible spelling errors. For example, I want the program to return a 1 whether someone enters "wagon" or "waggon."

So for example, I need something like this:
=IF(ISNUMBER(SEARCH("wagon",A1,1)),1,2)

except... more like this
=IF(ISNUMBER(SEARCH(OR("wagon","waggon"),A1,1)),1,2)

Except that last one doesn't work. It seems like this should be simple but I must be missing something. Any ideas?

Many thanks!!!

I have a large amount of data in columns which has the format number.number.number.Initials e.g. 3.3.3.TYH. To use the data I need to get it into separate columns and so use the texts to colums function. The problem is that you can only do one column at a time and the sheet is about 40-50 columns wide. Is there any way to do this quicker possibly using a macro?

Hi, I got a list and I would like to select more than one item in the
list with ctrl but asa I click on an item, it chooses it and therefor I
can't choose more than one.
EX:
A
B
C
D

I would like to choose A and B and the result would be A;B in the cell.

Thanks

I am trying to create an academic progress form for college students that is
specific to our department's requirements. We have different categories of
classes that need to be completed: chemistry, biology, calculus, etc. Some of
these are specific, e.g. Biology 171; for others, students have options, e.g.
either Chemistry 151 or 161. We get a report on the courses students have
completed each semester, which I put into a separate worksheet. I want the
Excel form to automatically fill in course information in the appropriate
category spaces based on course report data. I am attempting to use VLOOKUP
to bring in specific data for the courses in these categories, but I'm stuck
on this question.
Q. How can I get Excel to look for more than one course that could fulfill
a requirement, e.g. the chemistry requirement?

I understand that if I sort my table by dept. name and then course number ,
then I can use VLOOKUP to get what I want. The table array reference would be
specific to a single dept so that only its sorted course numbers would be
included. I can then return at least the highest number course the student
has taken by using a lookup value that is larger than any course number in
the table. However, since this table will grow as courses are added over
time, the table array reference will need to change each time data is added.

Is this a more appropriate task for Access? I'm not familiar with how it
works.

Thanks,

I am having trouble refering to more than one sheet in an If/Then statement.

I am looking to do

If sheet2(cell1)>100 then sheet1(cell1)=sheet2(cell1)
Else sheet1(cell1)=sheet2(cell2)

Thank you to colonal18 and Gary's Student, through combining your advice I'v
managed to come up with this code which opens the file who's name is in the
current cell and opens it with the application I want.

Sub playfiles()
Dim PlayFile
PlayFile = Shell("C:Program FilesWinampwinamp.exe C:MP3" &
ActiveCell.Value, 1)
End Sub

The only problem is it doesn't work on files with more than one word in
their name. If the cell I have selected has a file with 2 words in its name
e.g. Happy Birthday.mp3 - it opens WinAmp and treats it as if it was being
asked to open 2 files(one called Happy, one called Birthday) neither of which
exist.

The obvious (and impractical) way around this is to manually rename every
file with underscores_instead_of spaces.

Does anyone know a better solution?

Thanks

Phil

Hello,

I would like to achive the function below but it does not work to use & to include more than one condition. Is there any way to have more than one condition in a MATCH function?

{MATCH(TRUE;C12:C14<>C1 & C12:C14<>C2;0))}

The idea is to SEARCH a column and return the first position that is different from C1 and different from C12.

Is there a way of having more than one person in a workbook without having one version as read-only ?

I have a workbook which accounts use to log invoices and another department want to place comments in that workbook without having to wait until its free?

Mark

I have a Sunday through Saturday work sheet.
I would like to know how would i add more than one amount in a single cell?

Can you enter more than one formula in a single cell? What I mean is, I want
to list two values in a cell. For example, if I have the following data:
100
200
300
400
500
and I want the cell to display the highest and lowest values like: 500 100
can I somehow enter LARGE(A1:A5,1) &"" LARGE(A1:A5,5)
or do I have to use two cells?

--
Shirley

Hi I want to use more than one line in a MsgBox pop up? What bit of code (if possible) do I need to do this?

Thanks in advance guys.

I would like to be able to test more than one variable in an if statement.
Such as the contents of a1 and the contents of a2. Is this possible?

How do I refer to a specific window when I have a file with multiple windows
open. I originally wrote a macro to operate on a workbook with only one
window open. I used the workbooks("mybook").activate, but now that I have
more than one window open, it always selects the first window. If i am in the
second window when I run the macro, it puts the information in the active
cell in the first window of the workbook. How to I specifically refer to
window where the cursor is. Do I use some type of activewindow command? do i
create an object and set it equal to the current window? Thanks.

Each month I receive an Excel sheet with crew members currently on board different vessels. The sheet is divided, so the vessel names are in the "A" column, and the rank of the crew members are in row "1". That way, you can see the name of e.g. a Captain on board a specific vessel.

This sheet I normally format in a special way, in order to import it into an Access database.

There is one thing, which make it very time consuming, and that is when there has been change of crew members close to the 1st of every month. Then there might be two names in each field, and the database can't handle that. What I need, is a macro, or something similar, which can find each cell containing more than one name (some might have 2, 3 or 4 names and if more than one set of names in a cell, they are separated with a " / "). Then the macro should add the same number of rows, as number of names in the cell in question. The rows should have the same vessel name, as the row copied from. Each of the extra names should be in each of their own cells, below the cell they are copied from.

It is a bit difficult to describe, which is why I have attached a small example of such an Excel sheet. In the top it shows the initial formatting, and in the bottom it shows the result I'm after.

I hope somebody can assist. If additional info is needed, please let me know.

Thanks,
Anders

Hello.

I am a noob that is trying to create a super macro that consolidates all of my macros in to one (multiple people will be using it and will be confused with having to do multiple macros and which order to run them in). Essentially, I have to copy and paste data from a pdf invoice, paste it in to excel, then seperate all of the data. My first macro searches cells containing certain words and deletes the row. I have that macro down and it works properly. My next step is to cut and paste the first word from each column from its current cell to a new cell until I get to about the 4th entry within the cell (like text to columns, but more specific). I am currently using a macro I found on the internet; however, I can't use it consecutively. I can only run it once, then stop, select the new column, then run it again. When I attempt to have the macro select the column to be seperated, then seperate the first words, but when it gets to step two in the macro to continue about three more times, I get the error: Compile error: Duplicate declaration in current scope when it gets to the code in the macro, "DoAnyWay:", "nextrow:", and "terminated:". When I remove them, it kind of works but continues with a message box. I am also looking to see how to remove the message box so it can carry out and complete without having to click ok. Here is what the script looks like:

Sub SepTerm2()

   Columns("A:A").Select

  'David McRitchie  1998-08-05 [Ctrl+t]  documented in
  '  http://www.mvps.org/dmcritchie/excel/join.htm
  'Separate the first term from remainder, as in separating
  'street number as first item from street & remainder
  'Work on first column, cell to right must appear to be blank
  '--Application.ScreenUpdating = False
  'On Error Resume Next
      iRows = Selection.Rows.Count
  Set lastcell = Cells.SpecialCells(xlLastCell)
  mRow = lastcell.Row
  If mRow < iRows Then iRows = mRow 'not best but better than nothing
  For ir = 1 To iRows
       If Len(trim(Selection.Item(ir, 1).Offset(0, 1))) <> 0 Then
          iAnswer = MsgBox("Found non-blank in adjacent column -- " _
             & Selection.Item(ir, 1).Offset(0, 1) & " -- in " & _
             Selection.Item(ir, 1).Offset(0, 1).AddressLocal(0, 0) & _
             Chr(10) & "Press OK to process those than can be split", _
          vbOKCancel)
          If iAnswer = vbOK Then GoTo DoAnyWay
          GoTo terminated
       End If
  Next ir
DoAnyWay:
  For ir = 1 To iRows
       If Len(trim(Selection.Item(ir, 1).Offset(0, 1))) <> 0 Then GoTo nextrow
       checkx = trim(Selection.Item(ir, 1))
       L = Len(trim(Selection.Item(ir, 1)))
       If L < 3 Then GoTo nextrow
       For im = 2 To L
          If Mid(checkx, im, 1) = " " Then
             Selection.Item(ir, 1) = Left(checkx, im - 1)
             Selection.Item(ir, 1).Offset(0, 1) = trim(Mid(checkx, im + 1))
             GoTo nextrow
          End If
       Next im
nextrow:
  Next ir
terminated:

   Columns("B:B").Select

  'David McRitchie  1998-08-05 [Ctrl+t]  documented in
  '  http://www.mvps.org/dmcritchie/excel/join.htm
  'Separate the first term from remainder, as in separating
  'street number as first item from street & remainder
  'Work on first column, cell to right must appear to be blank
  '--Application.ScreenUpdating = False
  'On Error Resume Next
      iRows = Selection.Rows.Count
  Set lastcell = Cells.SpecialCells(xlLastCell)
  mRow = lastcell.Row
  If mRow < iRows Then iRows = mRow 'not best but better than nothing
  For ir = 1 To iRows
       If Len(trim(Selection.Item(ir, 1).Offset(0, 1))) <> 0 Then
          iAnswer = MsgBox("Found non-blank in adjacent column -- " _
             & Selection.Item(ir, 1).Offset(0, 1) & " -- in " & _
             Selection.Item(ir, 1).Offset(0, 1).AddressLocal(0, 0) & _
             Chr(10) & "Press OK to process those than can be split", _
          vbOKCancel)
          If iAnswer = vbOK Then GoTo DoAnyWay
          GoTo terminated
       End If
  Next ir
DoAnyWay:
  For ir = 1 To iRows
       If Len(trim(Selection.Item(ir, 1).Offset(0, 1))) <> 0 Then GoTo nextrow
       checkx = trim(Selection.Item(ir, 1))
       L = Len(trim(Selection.Item(ir, 1)))
       If L < 3 Then GoTo nextrow
       For im = 2 To L
          If Mid(checkx, im, 1) = " " Then
             Selection.Item(ir, 1) = Left(checkx, im - 1)
             Selection.Item(ir, 1).Offset(0, 1) = trim(Mid(checkx, im + 1))
             GoTo nextrow
          End If
       Next im
nextrow:
  Next ir
terminated:

End Sub
Once I can figure out how to repeat this code, I can get the rest to work. The data contains an example of the following:

Month Day Year Time Country Number Type Minutes Charge
Month Day Year Time Country Number Type Minutes Charge
Month Day Year Time Country Number Type Minutes Charge
Month Day Year Time City, State Number Type Minutes Charge
Month Day Year Time Country Number Type Minutes Charge
Month Day Year Time Country Number Type Minutes Charge
Month Day Year Time Country Number Type Minutes Charge
Month Day Year Time Country Number Type Minutes Charge
Month Day Year Time Country Number Type Minutes Charge
Month Day Year Time Country Number Type Minutes Charge

Text to column will not work because the data is unpredicatable and can be more of a hassle to fix. Any help is appreciated. I also attached an example of what it looks like and what it should look like in the end.

I have a workbook that must show me the missing numbers. I found a formula on this website which helped alot,but it only gave me the first missing number and not more than one. I am currently using the following formula to indentify the missing numbers:
=if(or(03=0,o3=1),"",text(p1+1,"0")&if(p3-p2=2,"","-"&text(p3-1,"0")))

Column O = whether we received money 1, received no money 0
Column P is a date converted to a number

My problem with this formula is that is gives me the following answer where more than one skip in the number appears:
178-181
where I would like it to appear as follows:
178
179
180
181
Is there a formula that can correct this.
Many thanks

I continue to get this error message "The specified field [Expendable Breakdown].[Supplier Code] could refer to more than one table listed in the FROM clause of your SQL statement" when running these two queries.


	VB:
	
 [CountOfAudit Date] 
FROM [5 Week Review - Total Discrepancies per Supplier] INNER JOIN ([5 Week Review - Date Compile] INNER JOIN [Expendable
Breakdown] ON [5 Week Review - Date Compile].[Audit Date]=[Expendable Breakdown].[Audit Date]) ON [5 Week Review - Total
Discrepancies per Supplier].[Supplier Code]=[Expendable Breakdown].[Supplier Code] 
GROUP BY [5 Week Review - Total Discrepancies per Supplier].[Supplier Code], [5 Week Review - Total Discrepancies per
Supplier].[Supplier Name], [5 Week Review - Date Compile].[Audit Date] 
ORDER BY [5 Week Review - Total Discrepancies per Supplier].[Supplier Code] DESC; 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

	VB:
	
 [CountOfSupplier Code] 
FROM [5 Week Review - Information] INNER JOIN [5 Week Review - SC / SN] ON [5 Week Review - Information].[Supplier Code]=[5
Week Review - SC / SN].[Supplier Code] 
GROUP BY [5 Week Review - Information].[Supplier Code], [5 Week Review - SC / SN].[Supplier Name] 
ORDER BY Count([5 Week Review - Information].[Supplier Code]) DESC; 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Access won't let me view the queries in design view due to the error and I'm not very good and understanding/ reading SQL statement.

Can anyone give these a quick once over to see what could possible be the issue? Thanks!

Hello,

I used the code below for Conditional Formatting. This works fine but the VBA-code crashes when I delete more than one selected cell. Is there a simple modification possible to prevent this from happening?

RF


	VB:
	
 Range) 
    Dim icolor As Integer 
     
    If Not Intersect(Target, Range("A1:C250")) Is Nothing Then 
        Select Case Target 
        Case 1 
            icolor = 6 
        Case 2 
            icolor = 12 
        Case 3 
            icolor = 7 
        Case 4 
            icolor = 53 
        Case 5 
            icolor = 15 
        Case 6 
            icolor = 42 
        Case Else 
             'Whatever
        End Select 
         
        Target.Interior.ColorIndex = icolor 
    End If 
     
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


Hi. I need to countif with more than one criteria, how do I do that?
Speadsheet Example:

Column A Column B
Novi 13
Novi 13
North 13

In this example I want to count only when Column A = Novi. Maybe a
different formula is appropriate. Thanks

In my workbook, I have many named ranges. Some of my named ranges have been given more than one name. I am looping through all the used cells and checking for cells whose names start with a certain prefix. I can usually do this easily by using the .name.name property of each cell, but I come accross problems when I get to ranges with more than one name. Ranges with multiple names only return the name that is first alphabetically when I use the .name.name property.

Is there anyway to access all the names of a given range?

Thanks,
Travis

Hi

When I open the Excel Programme or open the programme by selecting a saved workbook more than one workbook opens.

Originally I just got 2 previously saved work books, now I am getting 3.

I have checked the Macros section, and the xlstart folder but there does not seem to be anything here which could cause the problem.

Any suggestions please.

Hi,

Simple enough question, I cannot seem to open up more than one workbook i.e when I have a workbook open and then minimise and try to open a new workbook, the initial workbook appears, I then have to close this and open the new workbook, so I cannot tab between workbooks

Thanks,
kilyg

Can anyone tell me how to freeze more than one rows in version 2007? It
allows only a single row on the top or a single rom and column top and left,
respectively. I want to freeze the top 6 rows... This was possible in
previous Excel version but not in this one.

HELP please!
--
Angela


No luck finding an answer? You could always try Google.