ANmarS - Search results

Excel/VBA expert since Sep 1997 (20 years 9 months)

We are moving ...

We are moving to another location, better, faster and more resources .... check it out ANmar.Systems

7 Q & A posts tagged with TEXT

Wed
Dec 7
2011

How can I combine two cells into one with Enter between them?

I have cell A1 with name, B1 with Address, I want C1 to have NAme from A1 in one line, then the Address from B1 in line below, I don't want to go all over my 13000 cells to do that manually


Answer

That is very good question, here is what you need to do:
In C1, paste this:
= A1&CHAR( 10)&B1

You might need to select "Wrap Text" to show that

The CHAR(10) is what is called the Enter inside the cell.

Wednesday 12/7/2011 10:50:00 AM
Sat
Nov 19
2011

How do I make a input Text field in Excel 2010 and how do I extract the value of it into other formula?

I'm trying to place a text field in my excel document that would be visible and editable when saving as .PDF or .CSV

So that when users enter a Value, my tables would adjust according to what they filled in.
When I go to the Developers tab, I can't seem to select the Text field option :/

Any ideas on how to do this?


Answer

Developers toolbar, doen't have textbox control, because you have millions of them already in the sheet
each cell can be a textbox control
You can modify the layout/format of that sheet to make certain cell looks like a textbox

Or, you can use Control Toolbox textbox
http://folder8.com/View/?7U5WAWLP8K72

Let me know if that helped or not

Saturday 11/19/2011 12:40:05 AM
Fri
Nov 18
2011

How to save excel file to text file without spaces?

I have tried everything. I have coordinates in an excel file like this: x1y3 all together with no spaces and in one column. Every time I save it as a text file it comes out "x 1 y 3" I cannot have spaces or " in the file. How do I fix this other than manually? I have 800 coordinates and I'm not about to fix this by hand.


Answer

Save it as CSV file
CSV file = Comma Separated Values

Which will put , as separator

Then you just need to drag that csv file into a notepad window and save it as txt

good luck

Friday 11/18/2011 4:57:48 PM
Fri
Apr 2
2010

How do i set up a formula in excel using "choose"?

i've got this for example : "11.Jul.95" and need to use a formula ( which i guess is "choose") to pick the season like July - Summer
please helpp!!! :D


Answer

Actually it is like this
If your date is in cell B1, then in C1 paste this

=TEXT( B1,"mmmm")&" - "&CHOOSE( MONTH( B1),"Winter","Winter", "Spring","Spring","Spring", "Summer", "Summer","Summer","Fall","Fall","Fall", "Winter")

This will show it exactly how you want it

VBAXLMan

Friday 4/2/2010 12:56:16 PM
Wed
Jan 21
2009

How do you make a password field in excel so when you type in one of the cells, it comes up as asterisks *****?


Answer

Excel cell does not allow you to do this, you need to do an alternative ways, you got two:
1- You can use the TextBox from Forms toolbar (If you have Excel2003) or from Developer tab (If you have Excel2007)
2- Add a VBA form and add the text box to it.

Wether you do 1 or 2 you need to change the password property of it to make it show you * when you type in it

Again Excel cells can not allow you to do that, forget it

If you need help in doing this, or you don't know how to add a VBA form or Textbox to your Excel sheet, mail me here

VBAXLMan

Wednesday 1/21/2009 2:21:07 PM
Fri
Jan 16
2009

I want to convert 200 EXCEL files to TEXT files. Is there an easy way to do this?

I know I can do each one separately by opening it and saving it with a different format. But it would take a while to do this 300 times. There seems to be programs that can do this that cost money. Is there a free one. Any ideas? Thanks!


Answer

The free option is a macro inside Excel file, something like this

Sub ConvertAll()
ThisP= Thisworkbook.Path & "\"
For I=1 to 300
Workbooks.Open ThisP & "File" & i & ".xls"
ActiveWorkbook.SaveAs ThisP & "File" & i & ".txt" , xlUnicodeText
Next I
End Sub

Now this macro assumes:
1- You are having these 300 files in the same folder of the one having this macro
2- Your files are named as File1.xls, File2.xls, etc

You can change that macro as you like, or email me here to change it for you

VBAXLMan is back to feed your Excel needs

Friday 1/16/2009 10:07:55 AM
Tue
Dec 12
2006

This is regarding Concatenate function in Excel:?

I am trying to concatenate a Currency value ( $ 233,456,389) with a

percentage ( 74 %) in Excel using this function :


=CONCATENATE("$ ", TRUNC(B313)," @ ",TRUNC(C314)," % ")



I would like to have it as $ 233,456,389 @ 74%

Instread I am getting $ 233456389 @ 74% (currency value without commas)

Can someone help me in getting the commas in the currency display ?


Answer

Add this to the TRUNC function
TEXT(B313 ,"###,###")
Means the TRUNC would be
TRUNC(TEXT( B313, "###,###"))
and your final function will be

=CONCATENATE("$ ", TRUNC(TEXT( B313, "###,###"))," @ ",TRUNC(C314)," % ")

Enjoy my profile, I am the VBAXLMan

Tuesday 12/12/2006 11:03:16 PM

Tags

History