Replace Dots For Comma Both Columns?

Apr 7, 2014

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?

View 4 Replies


ADVERTISEMENT

Replace Dots In Dates With Valid Date Separator

Feb 19, 2009

I have a SAP application that outputs a field of data from a specific query. One column, the date, is produced in the format 18.02.2009. Is there any excel 'trickery' that can convert this into a standard british date format i.e. 18/02/2009 & then allow me to sort the entire data field by ascending date order?

View 6 Replies View Related

Replace Comma With Dot In TextBox

Dec 21, 2006

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

View 9 Replies View Related

Replace Empty Space With Comma?

Nov 26, 2013

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.

View 6 Replies View Related

Replace Blank Space With Comma

Feb 7, 2008

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............

View 9 Replies View Related

Replace Comma With Hard Return

Nov 14, 2006

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.

View 4 Replies View Related

Custom Format To Replace Decimal With Comma

Mar 21, 2008

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?

View 4 Replies View Related

Splitting Comma Delimited Numbers Into New Columns

Apr 20, 2009

I am using excel at the moment with a card playing program. using the excel sheet they provided the details of what cards are dealt are exported to the worksheet and there is a simple table like so

Player Cards
............................................................
Player 1/ 24, 27, 16
Player2/ 1, 5

The information is fed through one number at a time as the cards are dealt for a total of three rounds sometimes it is only two rounds and are delimited by a comma all in the same column. I would like if possible to have these numbers appear in separate columns. that is

Card 1 / Cards 2 / Card 3
Player 1
Player 2

IS this possible. briefly i want this to happen so I can use the Vlookup function as the numbers that come through each stand for a card value but using Vlookup only the first number works and the following return an NA value as it is impossible as far as I know to have every possible combination represented in a table . If there is a way of tweaking Vlookup so it recognises the comma delimiter and in the vlookup column it will show all converted numbers then i'm all ears otherwise any help on how to split would be much appreciated. Quickly I did try using the text to columns function when i did this however in the new destination it showed only the first number and discontinued showing the others in the original as well. Additionally in this function the 'preview of selected data' does not show selected data but some sort of link =programme_name_card_gamecard_1 somethig like that. Sorry for the long one.

View 12 Replies View Related

Split Multiple Comma Separated Columns Using VBA?

Jan 18, 2012

I am supposed to create a macro which will split the comma separated values into rows.

My main constraint is, I have 3 columns of comma separated values.

Let me explain with an example,
I have 5 columns namely:
A B C D E
123 1,2,3 1,2,3 123 1,2,3,4

Once I run a macro it should show like below:

A B C D E
123 1 1 123 1
123 1 1 123 2
123 1 1 123 3
123 1 1 123 4

[Code] .......

View 6 Replies View Related

Look Up Data From Multiple Columns And Insert In One Cell Using Comma To Separate?

Jan 31, 2014

I have a data set that I wish to look up the data from one column and if it is greater then 0 write it in another column separated by commas. Here is an example:

The data is dates that a service was provided and how many time that day it was done and not everyone gets the service on the same days. I would like to summarize the days of the month that service was provided not number of times into 1 cell.

A B C
Row 1 November
Row 2 1 5 15
Row 3 1 0 2
Row 4 0 1 3

November is in A3

If A2 is greater then 0 I want to write A1 A2 If A2 and A3 is greater then 0 I want to write A1 A2, A3 If A2 is 0 and B2 is greater then 0, I want to write A1 B2

View 14 Replies View Related

Split Comma Separated Text And Insert Into Next Row Retaining All Other Columns

Oct 26, 2013

Original table:
Column1
Column2
Column3
Column4

AAA
DDD
A1
X123, Y123, Z123

[Code] .......

View 1 Replies View Related

Connecting The Dots

May 2, 2007

I have 1 row of user entry cells (A1:Z1). To keep it simple, let’s say A1 is always 0 and Z1 is always 100.

The user can enter any positive number in any of the cells. For Case 1, let’s say ‘20’ in M1.
I want a set of formulas in A2:Z2 that fills in the values with straightlined values from 0 to 20 from A2:L2 and 20 to 100 in N2:Z2.

For Case 2, the user enters ‘10’ in M1 and ‘60’ in T1 and the formulas in A2:Z2 fill in the appropriate values that connect the empty cells (i.e., 0 to 10, then 10 to 60, then 60 to 100).

Basically, a user enters 1 or more numbers in 1 row, and the second row fills in all the non-entered cells with #s that are straightlined, thus ‘connecting the dots’.

I think a bunch of nested IF statements might work, but it’ll be hairy and nested IF statements are a resource hog. Any better ideas? I’m think maybe some INDEX and MATCH functions.

View 9 Replies View Related

Plot Dots On A Map With Data

Mar 30, 2014

I am planning on creating a map of a badminton court. I am hoping to record each hit of the shuttlecock with a dot on the map. Of course I want this to be inputted by data rather than manually inserting a circle shape and placing it in the correct location each time.

is there any way to do this in excel?

View 14 Replies View Related

Counting Black Dots

Dec 18, 2008

I use a black dot (Unicode hex 25cf ) and I would like
to count those dots.

I am using the formula below but it doesn't seem to work.

=COUNTIF(A9:A14,CHAR(25CF))

View 11 Replies View Related

Code To Join Dots

Dec 16, 2009

Cells E39:E53 are conditionally formatted to fill red if cell $AB$13=the cell 3 to its left .. eg: cell E39 would fill red if cell B39 (the cell 3 to the left of E39) is equal to cell $AB$13

Only one cell in the range will meet the condition at any time.

I also have the same range conditionally formatted to fill black if cell $AF$13=the cell 3 to its left .. eg: cell E39 would fill black if cell B39 (the cell 3 to the left of E39) is equal to cell $AF$13

Ranges F39:F53, and G39:G53, H39:H53 etc etc etc (all the way up to X39:X53) are all conditionally formatted the same way.

What I would like is for the cell that fills red in the first range to have a line connecting it to the cell that fills red in the second range, etc etc etc.

And the same for the cells that fill black.

Creating a chart from my data is not an option for other reasons, so I'd like to explore this possibility.

View 9 Replies View Related

VLOOKUP To Miss Blanks (the Dots Are Blank Cells)

Apr 15, 2008

I have about 90+ lists of data which looks like this: (the dots are blank cells)

Set 1
D0 Jimmy
...
D1 Keith
D2 Dave
...
...
D3 Sandra

Set 2
...
D0 Andy
D1 Ryan
...
...
...
...
...
D2 Lee

Each list was originally a set of names and i have populated the "D1, D2" cells (where D1 = the first name, D2 = the second, etc).

Is there any way, on a seperate sheet i can get a list which shows the following (ie. no spaces between lines of data):

Set 1 Set 2
Jimmy Andy
Keith Ryan
Dave Lee
Sandra

Is this possible through a VLOOKUP?

View 11 Replies View Related

Extract Middle Words From Text String With Multiple Dots?

Oct 22, 2012

black.blue.red.yellow.green.white

I'd like to extract just "blue.red.yellow.green" to the following spreadsheet column, keeping the intermediate dots, but getting rid of the first/last words and their succeeding or preceding dots.

I've tried using RIGHT, LEFT, MID formulas unsuccessfully

View 3 Replies View Related

Lookup Comma Separated Data And Return Comma Separated Answer

Mar 19, 2014

Comma separated data on sheet 2, look up info on sheet 1, return comma separated data on sheet 2.

Sample file attached : Book1.xlsx

View 3 Replies View Related

Find And Replace Based On Columns

Dec 27, 2006

Here is the problem: A1 contains : current model numbers, B and C contain new model numbers and corresponding old part numbers. Based on Column B and C I need it to go through and find all the old part numbers in column A and reaplce it with the new part numbers: Here is my table:

Current # NEW AFS# current #
10773650204020000216802606
10773652204022000116035305
10773653204023000115744805
10773654204023000215744806
10773657204023001215744881
10773658204023022715744844
10773659204024000115836205

For example, I need it to find all instances of "10773650" (first data in column A) and look in column C to find the corresponding number if it exists, if it does exist I need it to be replaced by the corresponding data in column B

View 10 Replies View Related

Text To Columns - Find And Replace

Jun 20, 2013

I work at a club. I've got a spreadsheet sent to me from another club we do business with. On it are charges from their club to ours that list the charge and associated member number.

I'm trying to use the text to columns feature to separate the member number from the other data in the column so I can sort by member number making it easier for us to bill our members.

Here is a sample of the data:

"Beach Club Bar,b500"
"Beach Club Bar,s200"
"Beach Club Bar,a150"

When I try to use text to columns what happens using the comma as a deliminator, everything including and after the comma disappears.

Thinking that maybe I needed a space between the comma and the member number (in the first line b500 is the member number) I tried to use find and replace to replace , with , and a space after it. This also resulted in all of the data including and after the comma disappearing. I'm sure I'm missing something here.

I tried copying the data into a separate spreadsheet. Tried changing it to text or general.

I typed in the exact data as you see it and tried the text to column function and it worked perfectly. What could be hidden in the data in that column that's causing this?

View 7 Replies View Related

Find Replace For Multiple Columns?

Oct 28, 2011

I have been put together some simple coding to find and replace a value (1) that i have obtained from a pivot table. My aim was to replace any "1" with the column header. The reason for this is that the cell(s) will be used in a VLookup at a late stage.

My problem is that I have the code to work for a single cell, but I want it to work for multiple columns so I don not have to create multiple macros.

I need to go from Column C to AW. I have a total row at the end - hence why I am using 'Step - 1'.

Code:
Sub FindReplace()
Dim i, lRow
Application.Calculation = xlCalculationManual

[Code]....

View 7 Replies View Related

Replace Multiple Columns Between Files?

Aug 24, 2012

I am trying to import 16 columns from one file into another (please reference the attached images). I need each line to match an identifier (in this case the players name (column A in MASTER and Column B in NEW)).

In the NEW file, each column to be imported is titled Stats1, Stats2, Stats 3 (up to 16.) These headers are identical in both files but a complication is that the NEW file only has 900 players (lines) and the MASTER has almost 1600.

Sidney Crosby's 16 Stats column from the NEW file needs to replace Sidney Crosby 16 different Stats columns in MASTER.

View 1 Replies View Related

Excluding Certain Columns With Find And Replace

Mar 22, 2013

I am writing a macro that will find several different values in sheet1 and replace them with values from a table in sheet2. It currently does exactly that but I realized that there are instances when replacing hyphens isn't appropriate and will ruin the upload of my data. I need to replace it in the rest of the columns though. The headers will always be the same, such as "MST" , so I could use that as a conditional statement I just don't know how to go about it with the replace function.

Code:
Sub FindAndReplace()
Dim LastRow As Integer
Dim i As Integer 'Counter to loop through all the rows found in the table

'Finds the last row used in my replacement table and sets that as the searchable range

LastRow = Sheets("Sheet2").Cells.Find(What:="*", SearchOrder:=xlRows, SearchDirection:=xlPrevious,

[Code] ..........

View 2 Replies View Related

Compare Columns And Replace Values Without Overwriting

Mar 14, 2013

I have 2 sheets similar to below :

Sheet1
apple pc
sony camera
lenovotablet
apple laptop

Sheet2
sony television
lenovopc

I need to compare Column A from Sheet2 with Sheet1 and where the values match, only replace THOSE values in Column B of Sheet1 with those in Column B of Sheet2. Hence, after the replacement, Sheet1 should look like (value for apple remains unchanged).

apple pc
sony television
lenovopc
apple laptop

What would be a formula that would do it ?

View 1 Replies View Related

VBA Solution To Compare/replace Data Between Columns

Jul 4, 2009

I have two columns of adjacent data Call them A & B (although this could differ)

If the data in the second colum is not equal to, blank cell or contains the words "CAT", "DOG" or "ELEPHANT" then the content of the cell in the second column should REPLACE the content of the adjacent cell in column A (in this example) leaving B blank, otherwise column B remains unchanged.

View 9 Replies View Related

Replace Text In X Columns Based On X Criteria

Oct 17, 2007

I've attatched the workbook here with some dummy values in place of financial figures (We don't base our profit on RAND() formula's )

Essentially, I would like to search for CAN in A and SER in B and if they're found, to alter them to SYD (A) and CAN (2). It's essentially an automatic find and replace, but based on 2 criterias rather than one.

View 9 Replies View Related

Find & Replace In Hidden Rows Or Columns

Mar 19, 2008

I need to find, then replace a particular value ("/0"), however, some cells may be hidden. And I just discovered that I can not replace the value of a hidden cell! Am I missing something, or do I have to unhide the row/column that the cell is in and then replace it? ...this does not replace the "/0" with "0" if the row the cell is in is hidden.

Sub test()

Dim rMatch
Set rMatch = ActiveSheet.Cells.Find(What:="/0", LookIn:=xlValues, LookAt:=xlWhole)
rMatch.Value = "0"

End Sub

View 3 Replies View Related

Search Across Columns By Row & Replace Specific Cell Contents

May 2, 2008

I have created a desk planning spreadsheet in excel. Each week a mailmerge sends out an email to everyone in the office asking them to confirm when they will need a desk in the next week.

As the normal employee doesnt get involved in desk planning I want them simply to state whether they will be in the building or not by entering "GH" (our building) in a cell under each day in the email they get sent.

When the reply comes in we select the cells from the email and then paste them into the desk planning spreadsheet.
-----

WHAT I NEED TO DO:
Select some cells (must be in the same row - that we have just pasted in).
Press a button on the Worksheet labelled "Auto Assign Desk", which does this:-
For each cell that contains "GH", replace with the right-most cell to the left of the selection that starts "GH_"... (this is the start of a unique desk reference (GH_1_1, GH_1_2,etc.)
-----

This will assign the employee to the desk they were sat at last, when they are in the office next week.

View 7 Replies View Related

Excel 2003 :: Find And Replace Missing The Ability To Replace Values?

Feb 5, 2009

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.

View 6 Replies View Related

Find And Replace To Not Replace Characters Found As Wildcards?

Jul 2, 2013

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?

View 4 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved