Get instant live Excel expert help with Text Functions

“My Excelchat expert helped me in less than 20 minutes, saving me what would have been 5 hours of work!”

Post your problem and you’ll get expert help in seconds.

Our professional experts are available now. Your privacy is guaranteed.

Here are some problems that our users have asked and received explanations on

I have a large amount of data that I need to sort based on descending numerical order (the values are in column A), and the data is in table format—most of the data is text, and Excel won't sort the data all together because the cells are of a different size. I need the data that is in each row to stay together, because it is specific to that row—so I'm having a bit of trouble with this spreadsheet right now, and I don't know how to cleanly get the data sorted without having to manually sort it.
Solved by E. J. in 29 mins
Hi I need help for a formula to convert text into columns in excel. 100150!Abacavir Ziagen 20mg/mL 240mL Mixture 240mL!553036!CP use 100151!Abacavir - Lamivudine Kivexa 600mg-300mg Tablet 30!553036!CP use 100152!Abacavir - Lamivudine - Zidovudine Trizivir 300-150-300mg Tablet 60!553036!CP use 100155!Abacavir Ziagen 300mg Tablet 60!553036!CP use 100200!Abciximab Reopro 10mg/5mL Inj 1!550131!CP use 100201!Filter - Sterile Durapore PVDF 0.22 micron For Use with Reopro 33mm Unit 50!556031!CP use 100210!Abatacept Orencia 250mg Inj 1!554006!CP use 100230!Acamprosate Campral 333mg Tablet 180!554231!CP use 100250!Acarbose Glucobay 50mg Tablet 90!550076!CP use 100255!Acarbose Glucobay 100mg Tablet 90!550076!CP use 100831!Acetazolamide Glaumox 500mg Inj 1!555111!CP use 101047!Acetazolamide Diamox 250mg Tablet 100!555111!CP use 101400!Acetone Merck 100mL Solution 1!556031!CP use 101408!Acetone Merck 2.5L Solution 1!556031!CP use 101792!Acetylcholine Miochol E 20mg/2mL Solution 1!555111!CP use 101987!Acetylcysteine OmegaPharm 800mg/4mL (20%) Inhalation 10!555031!CP use 101988!Acetylcysteine DBL 2g/10mL Inj 10!556031!CP use 102100!Acitretin Neotigason 10mg Capsule 100!551051!CP use 102115!Acitretin Neotigason 25mg Capsule 100!551051!CP use 102500!Adalimumab Humira 40mg/0.8mL Prefilled Syr 2!554006!CP use 102503!Adalimumab Humira 40mg/0.8mL Prefilled Syr 6!554006!CP use 102507!Adalimumab cartridge Humira 40mg/0.8mL Pen 6!554006!CP use 102508!Adalimumab Humira 20mg/0.4mL Prefilled Syr 2!554006!CP use 102509!Adalimumab cartridge Humira 40mg/0.8mL Pen 4!554006!CP use 102510!Adalimumab cartridge Humira 40mg/0.8mL Pen 2!554006!CP use
Solved by X. Q. in 17 mins
I would like to copy a row from one sheet to another sheet on the next empty row when the original sheet displays certain text.
Solved by B. W. in 30 mins
I need cells to calculate the hours worked from the rota. however there will be times where i input text or numbers that are not time related, if this is the case i want the cells to output 00:00
Solved by V. U. in 16 mins
I would like to enter a text into a field which will represent a certain value. The value is determined by the characters of the text. Each character corresponds to a specific number. The value should be the sum of the corresponding numbers. Thanks
Solved by B. C. in 18 mins
I need a VBA/Macros script and/or formula. I have a column of cells. Each cell contains a combination of text, numbers, and currency amounts ($). Some cells have only one currency amount while others have up to 6 different currency amounts. All cells contains some combination of currency amounts plus miscellaneous text and numbers. I need to create a 2nd column that SUM all the currency amounts in each cell, while ignoring all the rest of the text and numbers in each cell. I have uploaded a file with several example sells. I've worked with two great experts already on this problem and both have been very helpful.
Solved by S. H. in 18 mins
I am using Countif to check one column of words against another and seeing if they partially match and it should return a 1 or a 0 each row. The problem is that the cells are not just text, they are formulas, and it seems like countif does not work when the cells contain formulas because when I run the COUNTIF I get all 0s, even though I can see some words are the same. How can I fix this? Or is there another formula that works better.
Solved by I. J. in 22 mins
I want to change the color of the text in column c to green based if there is or is not a date in column j. Also if column j did not have a date, column c would change the text color in column c to yellow if there is a date in column h. Finally, if both column j and h both do not have a date the text in column c should change to red.
Solved by V. U. in 26 mins
Keep in mind the following instructions: 1. "Source" sheet is the file that contains your database 2. You wil have to add a blank sheet to workbook and name it Destination 3. .pdf and .xlsx and more are the extensions extracted to Destination using this code. 4. Run the code once, all .pdf extension files will be xtracted to Destination, then run the same code again, all .xlsx will be extracted to Destination below the .pdf's Sub Extraction() Dim xRg As Range Dim xCell As Range Dim I As Long Dim J As Long I = Worksheets("Source").UsedRange.Rows.Count J = Worksheets("Destination").UsedRange.Rows.Count If J = 1 Then If Application.WorksheetFunction.CountA(Worksheets("Destination").UsedRange) = 0 Then J = 0 End If Set xRg = Worksheets("Source").Range("F2:F" & I) On Error Resume Next Application.ScreenUpdating = False For Each xCell In xRg If CStr(xCell.Value) = ".pdf" Or CStr(xCell.Value) = ".xlsx" Or CStr(xCell.Value) = ".xls" Or CStr(xCell.Value) = ".doc" Or CStr(xCell.Value) = ".docx" Then xCell.EntireRow.Copy Destination:=Worksheets("Destination").Range("A" & J + 1) xCell.EntireRow.Delete J = J + 1 End If Next Application.ScreenUpdating = True End Sub Code Snips for re-use To Search for Extensions in bulk Sub ConvertWordsToPdfs() Dim directory As String directory = "C:\Wordup" ' The starting directory Dim fso, newFile, folder, files Set fso = CreateObject("Scripting.FileSystemObject") Set folder = fso.GetFolder(directory) Set files = folder.files For Each file In files Dim newName As String newName = Replace(file.Path, ".doc", ".pdf") Documents.Open FileName:=file.Path, _ ConfirmConversions:=False, ReadOnly:=False, AddToRecentFiles:=False, _ PasswordDocument:="", PasswordTemplate:="", Revert:=False, _ WritePasswordDocument:="", WritePasswordTemplate:="", Format:= _ wdOpenFormatAuto, XMLTransform:="" ActiveDocument.ExportAsFixedFormat OutputFileName:=newName, _ ExportFormat:=wdExportFormatPDF, OpenAfterExport:=False, OptimizeFor:= _ wdExportOptimizeForPrint, Range:=wdExportAllDocument, From:=1, To:=1, _ Item:=wdExportDocumentContent, IncludeDocProps:=True, KeepIRM:=True, _ CreateBookmarks:=wdExportCreateNoBookmarks, DocStructureTags:=True, _ BitmapMissingFonts:=True, UseISO19005_1:=False ActiveDocument.Close Next End Sub OPENING MULTIPLE FILES Sub OpenMultipleFiles() Dim Filter As String, Title As String, msg As String Dim i As Integer, FilterIndex As Integer Dim Filename As Variant ' File filters Filter = "Excel Files (*.xls),*.xls," & _ "Text Files (*.txt),*.txt," & _ "All Files (*.*),*.*" ' Default filter to *.* FilterIndex = 3 ' Set Dialog Caption Title = "Select File(s) to Open" ' Select Start Drive & Path ChDrive ("E") ChDir ("E:\Chapters\chap14") With Application ' Set File Name Array to selected Files (allow multiple) Filename = .GetOpenFilename(Filter, FilterIndex, Title, , True) ' Reset Start Drive/Path ChDrive (Left(.DefaultFilePath, 1)) ChDir (.DefaultFilePath) End With ' Exit on Cancel If Not IsArray(Filename) Then MsgBox "No file was selected." Exit Sub End If ' Open Files For i = LBound(Filename) To UBound(Filename) msg = msg & Filename(i) & vbCrLf ' This can be removed Workbooks.Open Filename(i) Next i MsgBox msg, vbInformation, "Files Opened"' This can be removed End Sub
Solved by G. Y. in 23 mins
I am trying to build an equation that supports two variables to create a sum. The specific calculations I am trying to create are highlighted in yellow from 18-32. You will see the text in red with the results I am trying to achieve. Please help and Thank you!
Solved by M. A. in 27 mins