My problem is that I am trying to use REPLACE function but I do not know where my starting point is.
I have last name first that you don't know how many characters will be, then empty space which is 7 characters long. How can I tell REPLACE formula where to start replacing empty spaces with comma?
Example:
Johnson John
I need to replace with Johnson, John.
I have the following code which is save Sheet as a text file. I know there are some options in Excel for saving, but it is not saving as ".txt" file.
I would like to modify this code to replace blank space with comma between Cells.
PHP
Private Sub CommandButton1_Click()
Application.DisplayAlerts = False
' Save file name and path into a variable
template_file = ActiveWorkbook.FullName
' Default directory would be c:temp. Users however will have the ability to change where to save the file if need be.
' Notice that i'm only allowing the save as option to be of .txt format.
' I'm also attaching the current date to the file name............
I have many sheets in my workbook with many filled or unfilled cells... I want that any cell in my whole workbook should be filled with " " ie a space if it is currently blank (ie don't touch the cells which have some data) and as there are many cells in a sheet .. I don't want to fill them with spaces as the file size will increase... I just want to do this for cells A-1 to AB-200.
What is the way of doing this with a formula or programming ie without macros... (or is macro the only way?) What if I just want to do this to a sheet and not to the whole workbook.
Some code where i can add a space after each comma in a string?
I would like to do this VBA. How to do this formula as there could be several of commas in the string
Any easy way to convert Smith John to Smith,John? No need to lose the space.
View 5 Replies View RelatedI have a list that contains names in the format
Lastname, first name
But some of the names have no space after the comma - which is what I am trying to achieve here
For example,
Citizen, Joe
Citizen,Joe
I would like to insert a space after every comma in the name, when there is no space. I am looking at a formula based approach to solve this.
I am trying to create a macro that will create a comma and space between every 5th character within a cell. that would be preference #1. What I decided to go with for now, instead, is to try (using the macro recorder) text to columns, fixed width, and do this after every 5th character, and selecting each cell to be formatted as text.
What I am having difficulties with is trying to tell the macro which cells to touch and which ones not to. I do not want the macro to do text to columns and fixed width on simple text. I only want it to do the macro when it is a string of numbers that, during transfer from PDF to excel, have been concatenated into one cell. I also want the macro to do this for infinite number of rows. I have excel 2007.
I am attaching a small excel file with examples of data I am working with. I have saved this excel file as a 97-2003 version for those who do not have 2007. As I stated, if anybody can help me figure out how to do a simple insert comma space every 5th character where information has been concatenated, that would be preference. Also, I am not trying to change anything within column A. Only column B. On the sample file please note B3 - B6. Rows 5 & 6 are fine as they are. Other problem rows include B11 - B13
I have a file of names that has some undisplayable characters. I am trying to match against a different file. It works if I overtype the "blank" fields with a space. How can remove these trailing problem characters whatever they are? I tried CLEAN and TRIM and the mystery characters are still there.
View 2 Replies View RelatedI have several lists of hundreds of emails that I need to list in an email string via Lotus Notes. I need a space and comma in between each name, ex:
john.doe@dm.com, john.doe1@dm.com, john.doe2@dm.com
I am using the below formula manually but it is taking too long. How to automate this? Is there a way to create the space and comma for as long as the list is?
=CONCATENATE(B6,", ",B7,", ",B8,", ",B9,", ",B10,", ",B12,", ",B13,", ",B14,", ",B15,", ",B16,", ",B17)
This is a delima I cannot figure out. I had to create passwords for a website we are building. I have 3000 employee numbers has to be used. So what i did was took the first initial and middle initial and last initial and first 5 of the ID number. I did a comma delimiter to obtain all of the letters and numbers. example: ABC12345
My problem is none of the passwords work because when I imported the letters and numbers into the sheet it looks just like the above. However on review I cut and pasted back to notepad and the data looks like this:
"A B C 12345"
So its adding a tab in the password thats thats a problem, How do I remove this extra white space between each comma delimited digit? without having to manually delete it ?
Table A: I have data separated by coma & space in cells A1:A4
A: Data Table
*A110, 20, 30, 40, 50, 60
211, 21, 31, 41, 51, 70
316, 19, 37, 48, 59, 81
418, 23, 33, 42, 57, 78
Table B: I want to calculate difference between cells A1:A2, A2:A3 and so on…
And if it is possible as shown in cell B2:B4
B: Result Table
*AB110, 20, 30, 40, 50, 60
211, 21, 31, 41, 51, 70
+1, +1, +1, +1, +1, +1
316, 19, 37, 48, 59, 81
+5, -2, +6, +7, +9, +11
418, 23, 33, 42, 57, 78
+2, +4, -4, -6, -2, -3
Separating cells A1:A4 data in separate cells can do this.
But if it is possible solution can be done as shown by VBA will be greater.
I want to set at my textbox,so that if user enter the number with comma instead of dot,the comma will automatically be changed into my default format(dot).for example,if user type in (86,5),the value will be converted to (86.5). I have read through the relevant previous thread regarding this matter and I wrote the function as below.It happens to be that (86,5) will change to (865.00),not as what it suppose to be.So can anyone correct my function below?
Private Sub txtdiameter_Change()
If txtdiameter = vbNullString Then Exit Sub
If Not IsNumeric(txtdiameter) Then
MsgBox "Numbers Only"
txtdiameter = "86" 'default value'
End If
End Sub
I got VBA line that will replace Dots(.) for Comma's (,)
[Code] .........
The problem is its only take collum I and i want it so that it will take I and J.
am I doing something wrong here that it only take I and not Both ? I and J?
I have a string in a cell which is essentially comma separated and want it in the same cell but with enters instead of commas. Cell info to start with would look like this
Bob, The, Builder
And the result I am looking for is
Bob
The
Builder
All within the same cell so I can create a label which uses this information on a user form. I have attached a small example.
I am having problems applying custom format to numbers that need to be formatted.
custom format use is 0",00". When I apply the custom format above to whole numbers, I achieve the results I want:
0.00
20.00
40.00
becomes
0,00
20,00
40,00
However, when I apply to numbers with decimal places
0.20.......
how I can change the custom format so that the period is replaced with a comma but the decimal values are preserved?
I am looking a macro which should perform below activities
I have a lists of suppliers which needs to cleansed....
1.replace all the special characters with a space
2.replace Corporation with Corp
Incorporation with Inc
Limited with Ltd
Limited partnership/ltd partnership with LP
Company with Co
ASSC/ASSOC with ASSOCIATION/ASSOCIATES (SEARCH SUPPLIER IN GOOGLE FOR THE CORRECT ONE)
MGNT with MANAGEMENT
SVCS with SERVICES
DEPT with DEPARTMENT
Unltd with UNLIMITED
Ctr with CENTER
UNIV with UNIVERSITY
3. Remove any text, Special characters and numbers after INC,CO, LTD, CORP,LLP, LLC
The macro should when I select any particular column... not restricted to column A
I am looking to replace the space in between numbers and letters in a text string. The number of entries varies with each row.
Example of the entries in the column I need to reformat. Each entry is in a new row.
Code:
10 SSS
24 MNL
17 HLG 18 LTN
17 CBG 17 HLG 15 HIL 15 thn 22 ALV
17 ELP 34 HLG 15 HIL18 THN 10 TTL
What I am looking to achieve:
Code:
10-SSS
24-MNL
17-HLG 18-LTN
17-CBG 17-HLG 15-HIL 15-thn 22-ALV
17-ELP 34-HLG 15-HIL 18-THN 10-TTL
I have special character that I removed with =CLEAN formula.
It was only one character which represents carriage return. It looks like one little square with question mark inside.
After I applied =CLEAN formula it disappeared, but now I don't have space between these two words.
How could I replace this special character with space?
I'm working with some data exported from a database. Unfortunately, when data is exported to excel, cells that should be completely blank (i.e. ISBLANK formula would return TRUE) actually contain empty strings.
What is the fastest way to replace the empty string with a true null?
I'm having this problem whereby a few cells in my work contain empty texts. An empty text is represented textually in VBA with a pair of double quotation marks with nothing inside (I think).
This means that the cells don't display anything visible, but are not blank/empty.
So what happens is that formulas such as ISBLANK(), COUNTA(), etc. are returning unexpected results.
I have a spreadsheet as attached where there are titles in row 3, but I want to go along each row and pull up all the data below to the immediate row below the title. This is different in each column but you will see what I mean. I do want this to be VBA and the real spreadsheet has many columns so it needs to cycle along I guess until Row 3 is empty?
View 2 Replies View RelatedIs there a way to extend the space of my userform beyond its maximum space? I have tried using vertical scroll bars but they were of no use.
View 1 Replies View RelatedI have been working on different formulas to return the text string between the first and last space and have been unsuccessful. Is this possible?
I have tried several combos or Left and Right, I have been able to get the values after the first space, and the values before the last space, but not between the spaces.
String: Y60
~C CULT NUCLEUS 3X2 SPRING WST BK XL
Desired results: D60
CULT NUCLEUS 3X2 SPRING WST BK
I have two words of differing character lengths separated by a space.
How can I remove the first word... essentially, all the charcters to the left of the space AND the space itself?
Comma separated data on sheet 2, look up info on sheet 1, return comma separated data on sheet 2.
Sample file attached : Book1.xlsx
I have the following formula that works fine until someone uses the space bar to clear a cells contents
=COUNTA($D11:$AI11)
When the space bar is used to clear a cells contents the COUNTA statements includes the space in the count. How do I count the number of cells with content and exclude the space bar space in a cell?
If find dot. with out space in ( A1 ) cell remove space after dot in cell ( B1 ) Cell, vb or macro
A
B
M V Micunovic
MICUNOVIC,M V
L.T.Kudrjavceva
KUDRJAVCEVA,L. T.
D Sumarac m.l.
M. L,D SUMARAC
I only want to remove one space at the end of my text within a cell, if there is a space.
Code:
Sub hth()
Dim c As Range
For Each c In Range("H1", Range("H" & Rows.Count).End(xlUp))
c.Value = Trim(c.Value)
Next c
End Sub
This problem has come up fairly frequently lately, and I'm not sure how to fix it, or if this is by design...but in Excel 2003 I can't seem to do a "Find and Replace" based on the value of a cell. I can do a find, based on cell value, but the moment I change to the replace tab, the "values" and "comments" are missing from the "look in" dropdown.
I've only noticed this when I'm trying to replace on a filtered list, so I'm not sure if that is part of the issue.
Perhaps an alternative way of arriving at the same goal. Basically I have a worksheet with a number of filtered columns. They are filtered just right, using custom filtering, and so I do not want to undo the filters. In some columns I have formulas that are returning #VALUE! errors. I'd like to replace all of these cells with NA.
I recently became owner of a spreadsheet with some issues, and I am trying to make it useful. Each row has a URL of a blog post, and I want to extract the date from it (which is present in each URL) while getting rid of the rest of the URL. I was able to get rid of everything up to the year (which comes first), but then the URL continues, for example, 2013/05/16/the-rest-of-the-url/ and I would like to just have 2013/05/16 remain.
I am trying to use find and replace with the find box reading 2013/??/??/*/ and replacing it with 2013/??/?? which effectively erases everything else in the url, but leaves ?? instead of the numbers. Is there any way to have it so that it keeps whatever was in the original box?