insert text in front of Text OR number - user defined format

[Simon-ch]

Hi,

Sorry if the question is a bit simple but I got stuck with this problem:
I can't seem to make user-defined format that puts a text in front of a number and/or a text.
Let's say I have A1: 13, A2: texttext A3: text7 and I want to format a lot of cells to "Ilike 13" / "Ilike texttext" / "Ilike text7"... ie add the same text in the front of the cell, no matter what the content is.
I did manage it seperately, with "texttext" @ for text and "texttext" # for numbers, but what's the general one ?
Sorry again if too simple, just can't figure it out.

[oldchippy]

Hi,

Can't you just format your cells to TEXT, then enter your contents, whether its a NUMBER or TEXTNUMBER

[Simon-ch]

that's what I've been trying to do, but sometimes I have dates as well, ie DD/MM/YYYY, and I can't just convert it to text properly and leave it like that... And if I format simple numbers to text, and then make a user-defined format: "blabla" @ it still just works with text (or number..)

[oldchippy]

Can you post a zip copy of your spreadsheet with some examples together with the result you require?

[Simon-ch]

I can't give the original sheet as it's from my business, but here what I need:

edit: Well, basically it's quite simple: a format that is is both "text" @, "text" # and "text" DD/MM/YYYY

[oldchippy]

Hi,

You could use this in B2

=IF(CELL("format",A2)="D1","This is: "&TEXT(A2,"dd/mm/yyyy"),"This is: "&A2) and copy down

[VBA Noob]

Just to add but may not be the solution for your problem

Custom formats

Number

"this is: "General

Date

"this is: " dd.mm.yyyy

Text

"this is: "@

VBA Noob

[Simon-ch]

@oldchippy:
Thanks, but I was trying to solve this with an easy format, so I can just reproduce it whenever I need it (often)
@VBA noob: That's exactly what I have been using to date, but I would need a universal one, because I have a lot of custom entries, eg; 13/07/2008 or 2005 or "date unknown" etc etc... So I could just select all and use one format to add a text in front of it.

[oldchippy]

Hi,

May be this will help?

[Simon-ch]

It does help, thank you, but I was looking for an easier way to do it actually.
Just thought there might be some way to do it directly, like combining different formats (ie text, date, numbers)

[oldchippy]

Not as a "General" excel format, but may be with VBA but I'm afraid that not my expertice - sorry

[VBA Noob]

Press Alt + F11 > Insert a module and paste in the below. Close VB editor then press Alt F8 then run FormatCells

Please Login or Register  to view this content.

VBA Noob

[Simon-ch]

Excellent, sorry for my late reply but this works perfectly !