1. D

    Remove Characters or Numbers of Variable Length from a String

    How would you trim the number from the street address? As you will see below the number and following street address are both variable length. The problem is when the "number" is variable length. Let's look at two scenarios... Thanks in Advance - Dan Address Desired Result 123 Main Street...
  2. O

    Vlookup() and Index(Match()) returning N/A, even though the lookup value exists in the range and is identical to that value within the range.

    Hi, Thanks in advance for your time. Im trying to match zipcodes in one column with zipcodes in another column in an array, to then return an associated value in another column in the array. I managed to make it work using vlookup with approximate values, but would prefer to use identical...
  3. D

    Completely cleaning account numbers and storing with a specific format

    Hi there, I'm interested in setting up VBA that I can use to completely clean account numbers and format them identically, no matter the data source. Just to explain my usecase a bit further, I'm bringing in account data from multiple locations and then running a lot of INDEX MATCH type of...
  4. N

    Combine LEFT RIGHT formulas with content from cell with ALT-enter text

    I am trying to extract content from a cell that contains text as followed (text between the brackets): "Yes - configurable Some text on that is not important" I am able to strip the "Yes - " using: =RIGHT(A1;LEN(A1)-(FIND("-";A1))) and I am able to extra the word before the return using...
  5. T

    Stripping quotation marks

    Hi, I am trying to strip the quotation marks on a page. I used the following in a subroutine, Range("A10") = Right(Range("A10"), Len(Range("A10")) - 1), to remove one quotation mark and received the following error message. . I would appreciate your help. Thank you. Joe
  6. D

    Clean, Trim, or otherwise remove all non-printing characters?

    Hi there, This topic has been brought up a few times, but the solutions I've found are not working too well. All I want to do is be able to remove all non-printing characters (including outliers like CHAR 160) and not have to worry about it breaking functions (primarily MATCH.) I'm not...
  7. R


    STRIM is a complete "space" removal solution to conveniently manage leading, trailing, or all spaces within a string STRIM stands for "space trim". STRIM allows for conveniently specifying what type of spaces to remove form a string. The choices are: leading spaces, trailing spaces, all spaces...
  8. K

    New error with TRIM function

    Hello everyone. I have been getting a VBA error message in an Excel file that has been in use for a few months with no problems until now. There have been no changes to the formulas or coding and the cells which are impacted by the VBA code is locked so no one can enter incorrect info or...
  9. T

    Potential Cell Character Limitation causing formula failure

    Hi Everyone, Perhaps I don't understand the separation formula I using well enough, but everything seemed to be working nicely up until data was entered beyond a certain point in a cell. Overall, I am using data imported from another source that is separated by double asterisks between terms...
  10. K

    VBA Code - Trim function

    Hi there, I need some help with the trim function in a code. I have a drop-down validation list in Excel with a 5 or 6 character number, hyphen, then text (see sample list below). I added the following code to the worksheet tab because I want to trim anything from the hyphen to the right...
  11. M

    Extract number with decimal from a text string cell

    Dear All, Appreciate your assistance to my inquiry below. I wanted to extract the highlighted decimal number (put in Column B) from the text string cell (Column A) as below. Anyone can assist on the formula? Source - text string cell in Column A 1/11/21 960 C P GL 71,959.26 71,959.26 CR...
  12. WildBurrow

    VBA to trim cell values for sheet name

    I'm stepping off the porch to play with the big dogs; I'm posting my first question and providing a sample of my code. I am using input message boxes to obtain info that will 1) populate cells on the worksheet, and 2) be used in the naming structure for the worksheet. The issue is that...
  13. R

    Removing Part of Cell/Text

    Hello - Trying to come up with formula that will give me the number from a different cell, but the cell I am trying to reference also had letters, Example: The cell referenced, lets call it A1, contains: '4 yrs'. I would like cell A2 to calculate 4. I tried doing a =LEFT(A1,1), but the issue I...
  14. I

    How to edit this copy and paste VBA code to not use a criteria if not found?

    Hello. I have this copy and paste code that copies cell values from one workbook to another. Copy from pickorder. Paste into wave planner. To match it up it gets the staging location in column D example, "STG.DD35" and the dsp in column E "M5DV." However sometimes this first sheet, referenced as...
  15. C

    Trim, Clean using Evaluate running into 256 character limit

    Hello I have large datasets that I run automated processes on that I need to trim and clean and have been using: Function CleanSheets(arrShtNames As Variant, startRow As Long) Dim ws As Worksheet Dim rng As Range Dim LR As Long, Lc As Long For Each ws In Worksheets(arrShtNames) With ws...
  16. I

    TRIM in VBA on a Range

    i have a range with 6000 rows, i want to apply a worksheet.TRIM function within VBA to eliminate spaces in-between and on the ends... problem is... nothing happens. whats best syntax for this? thanks <code> set rng = range("a1:a6000") With rng .value = WorksheetFunction.Trim(.value) End...
  17. R

    Need Excel Formula to trim spaces before Numbers

    Hi there, Please provide excel formula to trim spaces before numbers, i have received large data that contains spaces in each cell before the amount. I am using MS Office 2010. Thanks, Raaj.
  18. G

    Left Trimming VBA

    I'm struggling to find an easy way to simply trim a range of cells in a column (hard coded into VBA) and then left trimming by a number of my choosing (also hardcoded). I'm looking for something along the lines of this: Range("A1:A10").FormulaR1C1 = "=LEFT(TRIM(R[]C[],9))" But it's not working...
  19. M

    Check first & last letter from 2 columns against a column of words

    Hello, I'd like to check if a word contains matching first/last letters from 2 cells. I can't quite get it to work. In fact, it doesn't work at all - I over sold 'quite'. Eventually I will check MANY words for MANY different first/last matches so I want to automate obviously. Example data...
  20. F

    VBA trim data with space not from spacebar

    I have a two full columns of data that I need to trim. I can see the extra space at the end, but it's not a simple space from the spacebar. This data is downloaded from an Oracle database which as the source, I have no control over. I used =CODE(RIGHT(V2)) to see what the space is and I get...

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back