Find Or Replace Chr160 In Excel For Mac

I have a Firebird database stored in Windows-1251 codepage and managed using IBExpert. I have to get some billing info using SQL, edit it and then send it to clients. I export query results into.csv (comma-separated values) format and then process a bunch of csvs into a pretty xls (with borders, fonts, etc.) using Microsoft Excel 2010.

I have NO idea why, but IBExpert places a strange symbol everywhere in numeric values between decades (64 731 instead of 64731). Asc method from VBA tells me that it's the #160 symbol in ASCII codepage.NOW, the strangest observation I made: if you copy this symbol manually and delete it from everywhere using find/replace function of Excel, everything is OK. If you do the same thing in any text editor (e.g.

Good old notepad) everything is still OK. But when you try to automate the replacement using VBA, everything goes very, very wrong. No matter if you use a manually copied #160 from the csv itself or you generate it using Chr(160), if you try to delete all those, VBA also deletes half of the commas. By comma I mean generally known symbol #44, you can google 'ascii' pictures and check it out. I have to mark that again, the replacement affects half the commas, however all of them actually ARE the very same symbol, I rechecked that twice.You can look for a link to a csv below, so you can reassure yourself with the fact that I'm not crazy.Here is the code you can use to reproduce the magic Sub testWorksheets(1).UsedRange.Replace What:=Chr(160), Replacement:='End SubI'll be very thankful to someone who will clarify this phenomenon, because I just can't believe that VBA is that buggy, I think I missed something somewhereUPDATE: Guys, I am terribly sorry. I'm so dumb that I've uploaded the wrong csv.

Your comment about a comma suggests to me that it is type-converting the replaced texts from Text to a Number which is then displayed as a number without commas. Comma is either a thousand seperator in some locales or a decimal seperator in others. For example, if i replace ' ' in '234,567', the result is then type converted to a number of 234567 (displayed per number formatting) whether I want to or not. To preserve type you will need to use a loop or something other than.Replace, or.replace number format to Text before you remove the character–Nov 18 '14 at 18:30. I've imported the CSV to Range('A1'). Here's what I've found:. $F$2 = 4 708,200. Garry s mod v14 07 13 chevry.

We can remove carriage return character from excel cell by using a couple of methods, the first method is by using the replace method. Select the data range and press the Ctrl + H shortcut key. We need to first find what the value we are replacing is, and in this case, we are finding the “Carriage Return” character to insert this character.

That value is not detected as a numeric. This is due to the CHR(160) existing in the 2nd place ( the 'space' after 4). If you want that value to become 4708200 ( four million.), replace CHR(160) like you've done. New virtual dj broadcaster for mac. This removes the comma because now excel detects those values as numerics. Since you haven't provided correct info, Excel thinks the comma is a thousands separator.If it should rather be 4708,2 ( four thousand.), correct it during your CSV import:. To import the CSV correctly, you have to put CHR(160) as a thousands separator.

The comma will act as a decimal sign. This way, Excel will interpret 4 708,200 as the numeric value 4708,2 during import.When using REPLACE in VBA, Excel assumes the comma is a thousands separator. However, you haven't provided that it's NOT.:)Below is the code for importing the file correctly.

So, as @takl suggested, the solution was to modify thousand separator property.It's.TextFileThousandsSeparator if you are using ActiveSheet.QueryTables.Add method and.ThousandsSeparator if you are using Workbooks.OpenText method.I really appreciate his help, but I just have to use the Workbooks.OpenText method because it supports Local property. So, here's the edited file-processing loop from my script 'walk through selected filesFor Each SelectedItem In.SelectedItemsWorkbooks.OpenText Filename:=SelectedItem, Origin:=xlWindows, StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlTextQualifierNone, ConsecutiveDelimiter:=False, Semicolon:=True, ThousandsSeparator:=Chr(160), Local:=True.

.Within: Search just the current worksheet or the entire workbook.Search: Select whether to search first across the rows or down the columns.Look In: Select whether you want to search through the values or formula results, through the actual formulas, or if you want to look in the comments.Match Case: Check this box if you want your search to be case-specific.Match Entire Cell Contents: Check this box if you want your search results to list only the items that exactly match your search criteria. Specify search options on the Find tab in the Find and Replace dialog box.Click Find Next.Excel jumps to the first occurrence of the match. If this is not the entry you’re looking for, click Find Next again. Excel advises you if it doesn’t locate the data you’re searching for.Click Close when you’ve located the entry you want.You can use wildcards to search for data in your worksheets. (question mark) to indicate a single unknown character, or the. (asterisk) to indicate multiple unknown characters.

Replacing dataTo find and replace data in a worksheet, follow these steps.Click Find Next to locate the first occurrence or click Find All to display a list of all occurrences.You can sort the results of a Find All search by clicking a column heading.If you want to use the replacement data, click Replace.Excel performs the replacement and locates the next occurrence.If you want to replace all occurrences at the same time, click Replace All.Excel displays an information box indicating the number of replacements made.Click OK in the alert box and then click Close. About the Book Author Diane Koers owns and operates All Business Service, a software training and consulting business formed in 1988, that services the central Indiana area. Her area of expertise has long been in the word-processing, spreadsheet, and graphics areas of computing. She also provides training and support for Peachtree Accounting Software. Diane's authoring experience includes over 40 books on topics, such as PC security, Microsoft Windows, Microsoft Office, Microsoft Works, WordPerfect, Paint Shop Pro, Lotus SmartSuite, Quicken, Microsoft Money, and Peachtree Accounting.

Many of these titles have been translated into other languages, such as French, Dutch, Bulgarian, Spanish, and Greek. She has also developed and written numerous training manuals for her clients.Diane and her husband enjoy spending their free time fishing, traveling, and playing with their four grandsons and their Yorkshire Terrier.