Tracker
HOME    TRACKER    Excel

# Formula To Remove Spaces Between Words

## If I have ABC DEF in a1, is there a formula I can put in a2 to remove the space and display ABCDEF

Related Forum Messages:
Removing Spaces After Words
Spaces after entries keep messing up my VLOOKUPS. I get a lot of data from other people and when they have entered the information in some of the entries have a space after the word which mess up the results of my vlookups. Is there an easy way of going through and removing spaces after a word? Not all the words have spaces after them.

Counting String Gap/spaces/words
How can I count the number of gaps (spaces) of a string? (Adding one we have number of words!). I need to save the position of each gap (space) on a array. How can I do it? With InStr()? The variable (string) that i'm working is GlbTargetRange.Value

Split Function: Multiple Spaces Between Two Words
I am trying to parse a formatted text file. I am using the SPLIT function as

arr() = split(cline," ")

where cline is an input line. if the line input(cline) has multiple spaces between two words, how would it split that.

Eg :

abcd defg fefdcs fasdfasdsa

would the output of above be
arr(0) = abcd
arr(1) = defg
arr(2) = fefdcs
arr(3) = fasdfasdsa

Remove The Spaces
I am trying to remove spaces in front of a number (currently formatted as text)

I have tried "Text to Columns", "Trim", and other suggestions in previously threads.

I have copied bank statement amounts from an e-mail and the \$ amounts have one Space in front of them. When I use the Trim function, and then a paste special, I still cannot get rid of the space, and so cannot add up the amounts in this column.

Ex:

*7 Dec 2007*DEPOSIT*3,917.63
*7 Dec 2007*DEPOSIT*1,890.58

Remove The Spaces...
I have a cel that has about 100,000 phone numbers and at the end of some and before sum, there are spaces added to end. is there a formula, or a way to take everything away except for the 10 digit number?

Remove Trailing Spaces
I have written a function which works in the same way as the concatenate formula but where required it uses an underscore to make up the length (14 Characters) of the result. I seem however to have hit a minor snag when users input trailing spaces. I thought I could use trim to eliminate them but it doesnt seem to be working

Function HypCon(CorpAcct, Subdiv)

Dim n, i, iLen, iLen2 As Integer
Dim sCorpAcct, sSubdiv As String
Dim iLen3, iLen4 As Integer
iLen = Len(Trim(CorpAcct))
iLen2 = 7
iLen3 = Len(Trim(Subdiv))
iLen4 = 5..............

Remove Spaces And Stack
See attached. I want to use column A to create column B (the expected result) by way of a formula. Index?

Remove Spaces From Numbers
I wrote numbers in column b cells in disordered way :

1
.. 1
1
.... 1
.. 1
1

I would like to make them to be aligned in the left side by a code.

Cannot Remove Trailing Spaces
I have an excel file with two sheets . One containing the updated prices with its code ( Sheet1) and the other containing the old prices with the same codes (sheet2). Now after several trials to copy the new prices from sheet 1 to sheet 2 with check if the code is same . ( I couldn't )

So how to get rid of the 20 spaces from Sheet1 column A.

Remove Spaces From Text
I'm trying to use the SUBSTITUTE function to remove spaces from my cells in column A. The trouble is My formula is removing all the spaces from the cell. My formula is: SUBSTITUTE(A1," ","")

I do however have many cells that contain a space between characters 5 and 7 and spaces at the end of the cell which have been padded out to 15 characters long.Its only the spaces at the end of the cells I need to remove.

Remove Superfluous Spaces
I convert a .pdf report to excel using Able2Extract. The output is leaving some xtra spaces inside the word. For example,

Clean in g Co st

I tried running Dave McRitchie's Trimall on the text, Sample workbook attached

Remove Spaces From Selection
I have a macro that I run after selecting a column which removes spaces. However, it processes all 65536 rows and I only want it to process the cells with values. I need to enter into an input a column and then it just removes spaces from the cells with values

Sub NoSpaces()
Dim c As Range
For Each c In Selection.Cells
c = Replace(c, " ", "")
Next
End Sub

VBA To Remove Punctuation And Spaces
I need VBA to remove all punctuation and spaces from cells. What I want is to do this from the same columns every spreadhseet that I open and when it gets to row 700 to stop. So I guess a range for this example could be A1:B700.

Remove Trailing Spaces Not Working
The below code removes trailing spaces, but for some reason it does not remove space from this, i clicked F2 and the space is still there, why does it work on some but not others?

98956P102

Sub test()
With ActiveSheet.UsedRange
End With
End Sub

Remove Spaces From Imported Dates
I use a program that can export data into Excel. For the most part, it works fine. except it doesn't correctly handle dates. For some reason, when it pastes a date into excel it includes 8 or 9 (depending if the month is 1 or 2 digits) spaces in front of the date which makes calculations with these dates impossible unless you go into each cell and delete the spaces (or do Find / Replace).

Is there a way I can create a non-VBA formula that will reference any one of these cells and some how identify what the date is & then that cell can be the date I can calc on? I.e., A1 is the date with the spaces in front, B2 is the cell that will reference A1 and produce, as the contents of B2, the date in A1. I can then use B2 in calculations.

Note: the month formats possible are: M/DD/YY (Jan. - Sept.) & MM/DD/YY (Oct. - Dec.). There are no other variations.

Remove Trailing Spaces Vlookup
I am doing a vlookup on fields such as 02-0223. In one table, there is a space at the end of 02-0223 and in the other table, there is no space. How can I delete the space so the numbers match in vlookup?

Unable To Remove Spaces At Beginning Of A String
I am trying to get rid of the spaces at the beginning of text that is the result of a download from a reporting software package. I am using the Trim function but it does not work. It seems that it has something to do with the formatting. The first set of data of the download looks as follows:
SalesSales BreakdownSolenaceousCucurbitsLargeSeedIf I overtype the text in exactly the same way (thus keeping the spaces at the beginning), the trim function works. But this is not a practical solution.

The type of the cells is "2". I have tried copying the data as values to a different workbook but this does not help either.

Code To Remove Extra Spaces From Data.
I have a macro which opens one excel file, then copies the data into another, dead easy. However the first file is 'downloaded' from a bespoke package, where (for whatever reason) the package appends a number of spaces (" ") after data in one of the columns,

So sometimes the data will contain one, ten or more extra spaces (no telling how many) ie, it could look like "AB ", "AB ", or "AB " etc

Ideally What i need is a small bit of code that once the data has been imported to my sheet it can run and 'strip' extra spaces from the column, lets say column f, to leave all the data in this column to look like:

"AB"
"AF"
"CD1"
"VFE"

I am drawing a blank, any simple lines of code?

How To Remove Spaces And Combine 2 Cell Data To One
cell A1 has " HH001 " (without "", there is space infront and behind the data),

cell B1 has "motor gear case",

what formula to put in cell C1 to get like this "HH001" (removed spaces in A1)

and D1 to get like this, "[HH001] motor gear case"

Creating A Macro That Will Remove All Front Spaces
in creating a macro that will remove all frontspaces, trailing spaces from entire sheet + remove characters like (), *,-, &, @,/,',;,. from columns E and F,

in col E and F there should either be string or numbers, everything else should be removed.

Remove Spaces From ComboBox RowSource List
I have a UserForm with a ComboBox on it. The RowSource for this ComboBox is a named range called rInv. rInv has rows broken down by day and 105 columns broken down by items. The rows are broken bown to 31 day sections with 27 possible customer/Invoices per day. Most days have between 2 to 20 Customer/Invoices, which leaves anywhere from 7 to 25 blank entries per day. These blanks spaces won't allow the user to scroll past the 1st days Customers/Invoices!

Is there any code to remeve the empty spaces from being seen by the ComboBox RowSource? (I can't actually remove the spaces, they need to remain)

Writing Batch File To Remove Spaces From Filenames
I realise this is not strictly an excel question but it forms part of my VBA code within excel

I have been writing a VBA program (with some fantastic help from you guys) part of which writes to a batch file to rename files stored in a given folder. Unfortunately this fails if the original files have spaces in the filenames. The batch file contains command lines such as: ...

Search For & Remove Leading Or Trailing Spaces
I wrote a tool that people at work use. They initially need to paste in a bunch of customer locations with Address, City, State, Zip, etc. Sometimes the Users have "bad input" data that has non-breaking spaces, multiple space between words, or leading and trailing spaces and nonbreaking spaces. I have code to get rid of all of those problems. However, the Users often use their data for other important functions at work. So I want to give them a message to let them know that their Original Data is "bad".

So instead of just "Fix" . . . I want to "Report the problem", then "Fix". I need to identify exactly what problem was found - not just tell the User that their data is bad.

I wrote a simple Search routine with error handling that identifies 2 of the 4 cases and notifies the User:
Case 1) ASCII 160 (non-breaking space, HTML &nbsp
Case 2) multiple spaces (2 or more consecutive spaces)
Case 3) Leading or Trailing Spaces (ASCII 032)
Case 4 Leading or Trailing non-breaking spaces (ASCII 160, which is HTML &nbsp)

I cannot quite figure out how to find the 3rd and 4th Cases. If anyone can help me with Case 4 especially, then I can probably do the same thing for Case 3.

I think it will work to somehow use this idea - the code is not even real code but it is just conceptual:
RIGHT(CellReference, 1) = Char(160) or Char(032)
LEFT(CellReference, 1) = Char(160) or Char(032)

Anyway, here is what I have so far . . .

ErrorFlag = False
Cells.Select ' select entire worksheet

' BAD INPUT 1 - lLook for any occurence of ASCII 160 (non-breaking space, HTML &nbsp)
' and Notify the User if any of his Input cells contain &nbsp's
On Error Goto errormsg1
Selection.Find(What:=" ", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).Activate

Remove Leading & Trailing Spaces From Cells
While entering data space is given in the first and last of each cell content. For example

if there is a word Alex Patrix in a cell, space is given before A of alex and after x of Patrix. This is done fo many cells. I want to remove only initial and last space which is un-necessary.
The space caused problem to compare cell so i've to remove space.

I've around 2500 cells with this problem.

VLOOKUP To Remove Words
I'm if I can create a table of words that I want removed from a column.

For example:

Red Fast Cars
Red Slow Cars
Blue Fast Cars
Blue Slow Cars

Can I setup a VLOOKUP function to remove 'Red' and 'Blue'

I know I can create multiple work sheets and use Control + H, to remove words. But that entails a lot of cutting, pasting, and filtering. Plus this example is simple, I'll be using longer text strings AND some examples might need to have 3 words removed from the same string

Remove Certain Last Words From String
I'm attempting to extract the Street Names from an Address.

For Example, if given "123 Very Big Street" I'd want to extract "Very Big".

If given, "123 Very Big" I'd want the same answer.

I've written code to do this, with a simple IF statement to identify mathcing last words like "Street" and "Drive".

How can I efficiently do this with a large list of last words? I'm drawing a blank on how to query against an array that would contain all these values.

Remove 1st X Words From A Cell
I have a list of email addresses, about 2000 rows long. they are listed like so but in 1 cell Internet EMail Address: Jo.bloggs@avivagroup.com.au This is what is exactly in the cell, except different names. What I want to be able to do is run something to delete the Internet EMail Address: part but keep the actual address. The problem is its all in the one cell as stated

Any suggestions, list goes something like this

Remove Space And Symbols In Front Of Words
I have a spreadsheet that I exported from my accounting software. For some reason there are 4 columns that all begin with an apostrophe and have various lengths of white space before the wording begins.

What kind of formula can I use to get rid of the apostrophe and have the wording be left aligned within the cell with no space before or after it?

The file I am working on is attached. They are columns C, D, E and F.

Eliminating Spaces In Formula Value
I am having trouble with eliminating spaces from the value I've generated from my formula.

The formula will take the first 4 characters from a list of names, and add to that a number and a year. (For instance: "Conniff" would become "conn_01_09").

My problem is if I have "Ag Services". "ag s_01_09" would be generated. How can I eliminate that space? The list of names is in one field.

The formula I used so far is: =CONCATENATE(MID(E2,1,4),"_",MID(F2,1,2),"_",MID(C2,3,2))

I tried TRIM and CLEAN, but couldn't get them to work right.

Validation Formula To Prevent Spaces In Cell
I am in need of 2 validation macros (formulas) to enter into the validation criteria formula box when creating a custom validation.

1. A formula that will generate an error if there is a space in the text entry. Example:060107_Halo3CG prev.mpg would be an invalid entry due to the space after CG.

2. The same formula as above but this one also needs a 50 character limit added to the criteria.

Formula To Sort And Leave Spaces Blank Where One Column Doenst Have The Same Value
Every AM I run a report that has ALL of our company order numbers from the 2 systems we use. I get those reports and put them into 2 columns. instead of manually inserting so they all match up, is there a way to do this automatically? ....

Can We Unmerge Words With The Any Formula??
Can we unmerge a complete address in seperate seperate coloumns? Like Complete address is "1st Floor, 2a Harrison Road, Erdington, West Midlands, B24 9AA" and i want to split it like Address[1] "1st Floor" , Address[2]: "2a Harrison Road", Address[3]: "Erdington"...

Is there is any formula which can split data with the help of seperators (Comma or space) ??

Remove "TRUE" & "FALSE" Words From A Linked Checkbox
On the sheets where 'present' needs to be checked, after linking the checkbox to the cell it now says "TRUE" or "FALSE". How do I get rid of that? There is a formula that this affects also (just FYI).

Replace All The Times That These Words Appear In The Rest Of The Sheet With The Words In Column B
I have a column of words in Column A and I want to replace all the times that these words appear in the rest of the excel sheet with the words in Column B. If someone has already answered a similar problem link me to the thread because I can't find anything.

Formula To Calculate Values From Words
There are five levels of Performance.

Not Evident(o), Emerging(1),Operational(2),Highly Functional(4),Exemplary(5)

There are ten rows of Categories of Performance.
There is one final column of Performance Summary for each category.

There are seven columns that are report sources of Performance.
There is one final row of Performance Summary for each report source.

There is one cell that is a final Summary of Performance Overall.

The cells have drop down lists of words only.

I need the Summary cells to show the result of calculation of the average for each row and column.

Formula Cant Always Match Words From The Arrayand Gives Me N/A
The formula cant always match words from the arrayand gives me N/A

I have attached the formula i am trying to use . Is there an earier way anyone can recommend. Im sure im over complicating things

Formula To Count Words In Cells
I need a a formula that will count the number of times a word or phrase appears in a column of cells and assign a number

For example....

Big Red Ford Truck = 1
Ford Truck = 3
Red Ford Truck = 2

After i get the formula, I can sort by "greater than 2"

Auto-calculate 2 Column That Contains Numbers And Words Words
I've been using conventional method to do this and it's time consuming. I would like to total up 2 column. A multiply B to be exact. Below are some examples:

Table 1 - Before totaling up:

Quantity
Product
5
2 x Button A White
3
4 x Button B Pink
4
5 x Ribbon A Black
2
6
2 x Cloth A Blue

Table 2 - After totaling up:

Quantity
Product
10
Button A White
12
Button B Pink
20
Ribbon A Black
6
12
Cloth A Blue

I need to have the sum of the "Quantity" multiply "Product". Or in short A x B.
And the end result need to have the number and "x" sign removed while keeping on the the products names. (2 x ) Take note it's "number" space "symbol" space.

Parse Words From Cell Text Formula
"Use a formula to fill in column F (brand name) in the data worksheet. The Brand Name is the Branded Description minus the last word.

NOTE extra mark: If your formula can’t find a space (is error = true) then it takes whatever is in the cell and uses that."

Would I be using the CONCANATE formula or something similar?

Find Words - Deleting The Words - And Deleting Some Info After The Words.
I am trying to find certain words in a column and delete the word and characters following. For example, Say I have a column of info as seen below. This is a test of me. I am just experimenting with this stuff. Deleted (6/15/01) Let me know what you think. I am not sure about it all, but I guess I will figure it out. riviledge1 (01/05/06) Now let's see what happens when I try to test it.

I want to find all the "Priviledge1 (01/05/06)" and replace with nothing. Please note, the date will change with each record, so I need to figure out how to tell Excel to find "Priviledge1", delete it and the date behind it. So I want to delete "Priviledge1" and the next 11 characters including the space.

Question With Writing A Formula That Can Pick Out Words With 3 Letters
I have a question with picking out certain word in the column. I am trying to write a formula that would allow me to pick out names that has first 3 alphabetical letters from the columns. The column has a lot of different names, but the one that I would want my automation to pick out is similar to this, C, AR, AA-103, BG-2056, HJE-1100, etc. However, in the same column, I have other words like elevtar, hsvte, lvnrm, etc. that I do not want my formula to pick out.

Modifying A Sumproduct Formula To Search For Words In A Cell
My current formula is this: =SUMPRODUCT((Str311A=1)*(Str311C=15)*(Str311G<>" DM "),Str311L). All the items starting with Str311 are formula's associate with a name. What I'm trying to do is add one more factor into the formula. I need it to look in column I for any cell that contains the words L/S. I tried modifying the formula to this but it does not work:

SUMPRODUCT((Str311A=1)*(Str311C=15)*(Str311I="*L/S*")*(Str311G<>" DM "),Str311L)

The formula contained inside the names are:

Str311=OFFSET(Outlet!\$A\$1,MATCH("STORE # 163311",Outlet!\$A:\$A,0)-1,0,MATCH("TOTAL FOR STORE : 163311 SAN MARCOS",Outlet!\$A:\$A,0)-MATCH("STORE # 163311",Outlet!\$A:\$A,0)+1,1)

Str311A=OFFSET(Str311,0,0)

Str311C=OFFSET(Str311,0,2)

Str311I=OFFSET(Str311,0,8)

Str311G=OFFSET(Str311,0,6)

What I need is a way to modify my original formula to now also only match cells that contain the phrase L/S. The phrase can be located at the beggining, middle, or end of the cell characters.

Sort On Number Of Words (words With Least Characters First)
I want to sort on number of words, i.e. if a cell contains 1 or 2 words etc, with the cells containing 1 word coming first then cells containing 2 words. Also, if possible, first the cells with fewer characters.

Count Words In Range Mixed In With Other Words
I have tried Sumproduct and Countif and can not get a proper count of rows that contain specific words in multiple columns. I have attached an example spreadsheet where I am looking for a result of 7 rows that contain 1 to 4 specific words within phrases. The example looks for the words: "virus", "spyware", "malware" & "adaware", and ignores any other words like "aware" or "ware".

Combining List Of Words Into New Words, Conjugation
I have 4-5 separate lists of words/letters and I would like to combine them in a certain order but creating all possible combinations resulting a new word. It's like a conjugation tool for verbs. For example if List 1 = be/el/ki/meg; List 2= m List3=e/é; List4= gy/" "; List 5=" "/ek/sz/nnek So the output has to be (if the order is List1+List2+List3+List4+List5)= bemegy/bemegyek/bemegysz/bemesz/bemész/elmegy/elmennek/ ..... and all possible combinations.

Preferentially I would like to automate it in a way that it straight outputs into MS Access. Also can it be automated that it reads the list from a file (database)?
(Also can it recognize the word by letters? Because the basic word that I would load the program= List2+List3+List4)

Remove Formula From Cells
is there a way i can clear the formulas in column B. the value must remain only the formula should be removed...

also i want to avoid copy /paste method of doin it...

a VBA script would be great ..

Formula To Remove Text From Cells
I require a formula to remove all text and spaces from a cell, reporting back only the single digit number that is contained within.

A1 - Data to be manipulated (e.g. options look like "2UE", "UE2", "4P", "5 TW")
A2 - Result with all text and spaces removed (e.g. "2", "2", "4", "5")

Formula That Will Remove The First 2 Characters And The Last Character
i need a formula that will remove the first 2 characters and the last character from the below, so below the result should be R0131644, the number of characters vary from row to row, they are not always 11

EUR01316441

Formula To Remove Unwanted Characters
I am looking for a formula that will remove any unwanted characters in a cell.

For example i may a word or string of words with "-" , " ' " or "," in them and i would like to have these removed.

Formula To Remove All But The Last Of The Same Character In A Cell
I have data in cell A1 and A2 which looks like below

SECTOR - 11, HIRAN MAGRI, - 313001
MAIN BRANCH, 30-15-171 DABA GARDENS, NEAR SARASWATI PARK,-530020,

In cell A1 the number of hypens (-) are 2
In cell A2 the number of - are 3

What I am looking for is a formula which can remove all additional - except the last one. Therefore the result of the formula should be

SECTOR 11, HIRAN MAGRI, - 313001
MAIN BRANCH, 30 15 171 DABA GARDENS, NEAR SARASWATI PARK,-530020