Prefix query

[Steve]

i I have a number of text entries in a colum which I would like to
prefix with zz.

for example

David
John
Calum
Brian
Paul

I would liketo be

zzDavid
zzJohn
zzCalum

etc

At the moment I can only do that manually and wondered if there is a
shortcut to do it for multiple entries ?

Any help appreciated

Steve

 

[Claus Busch]

Hi Steve,

Am Sun, 03 Jun 2012 19:46:35 +0100 schrieb Steve:

for example

David
John
Calum
Brian
Paul

I would liketo be

zzDavid
zzJohn
zzCalum

the names in column A, then in a helper column:
="zz"&A1 and drag down, then copy the helper column and paste it back as
values


Regards
Claus Busch

 

[Claus Busch]

Hi Steve,

Am Sun, 03 Jun 2012 19:46:35 +0100 schrieb Steve:

I would liketo be

zzDavid
zzJohn
zzCalum

must it be zzDavid or can it be David and look like zzDavid?
If it has to be zzDavid, then do it with formula.
If it only have to look like zzDavid you can format the cells with
custom format:
"zz"@


Regards
Claus Busch

 

[Steve]

Hi Clause and yet again thanks for taking the time to help.

If I use the custom format option it does indeed look like the prefix
is zz but when I want to sort so that the zz entries are last in the
A-Z sort they dont move.

If I use the helper row it also doesnt work as the cells are non
sequential so when I try and copy and paste the new cells with zz
prefix excel tells me the copy and paste sizes are different. I guess
that is because I have filtered the cells to only those that I wantto
prefix with zz

Is there a way aroudn this?

thanks again

steve

 

[Claus Busch]

Hi Steve,

Am Sun, 03 Jun 2012 23:11:03 +0100 schrieb Steve:

If I use the helper row it also doesnt work as the cells are non
sequential so when I try and copy and paste the new cells with zz
prefix excel tells me the copy and paste sizes are different. I guess
that is because I have filtered the cells to only those that I wantto
prefix with zz

filter your cells to only those you want to prefix. Add your "zz" in a
helper column. Then copy the column, select only the first cell with the
name in your original column and paste special => paste values


Regards
Claus Busch

 

[Claus Busch]

Hi Steve,

it's me again.
Your names in column A. Filter the cells to only those that you want to
prefix. Helper column is column H. Add "zz" with the formula.
Copy the code in a standard module and run it:

Sub Prefix()
Dim LRow As Long
Dim rngC As Range

ActiveSheet.AutoFilterMode = False
LRow = Cells(Rows.Count, "A").End(xlUp).Row
For Each rngC In Range("H2:H" & LRow)
If Not IsEmpty(rngC) Then
rngC.Offset(0, -7) = rngC
End If
Next
End Sub


Regards
Claus Busch

 

[Steve]

Hi Claus, thanks again for your continued help.

I will try this solution later and feedback to you here

Kind regards


Steve

 

[Steve]

Hi Claus and thanks for the macro, it works perfectly.

I hope you dont mind but I have another query.

I have a list of numbers which run from left to right for example:

12456472
3245671
234
451
1210999


I would like to be able to attribute a number in another cell
depending on the numbers above as follows:

If the number ends in a 1 - the new cell would contain the number 5
If the number ends in a 2 - the new cell would contain the number 3
If the number ends in a 3 - the new cell would contain the number 2
If the number ends in a 4 - the new cell would contain the number 1
If the number ends in a 5 - the new cell would contain the number 1

Is that at all possible?

Thanks in advance

Steve

 

[Claus Busch]

Hi Steve,

Am Tue, 05 Jun 2012 18:27:00 +0100 schrieb Steve:

12456472
3245671
234
451
1210999

are these numbers in A1:A5?

I would like to be able to attribute a number in another cell
depending on the numbers above as follows:

In which cell is this number? Please give an example of this number. And
what cell do you mean with "new cell"?What do you mean with the number
ends in a1?

If the number ends in a 1 - the new cell would contain the number 5
If the number ends in a 2 - the new cell would contain the number 3
If the number ends in a 3 - the new cell would contain the number 2
If the number ends in a 4 - the new cell would contain the number 1
If the number ends in a 5 - the new cell would contain the number 1

Regards
Claus Busch

 

[Claus Busch]

Hi Steve,

do you use xl2007 or later? Then try it in GC1 with:
=IFERROR(CHOOSE(RIGHT(O1,1),5,3,2,1,1),0)
Else use:
=IF(ISERROR(CHOOSE(RIGHT(O1,1),5,3,2,1,1)),0,CHOOSE(RIGHT(O1,1),5,3,2,1,1))


Regards
Claus Busch

 

[Steve]

Excel 2007 Claus and thanks again it works perfectly.

Much appreciated.

I promise my next query will test you even more lol

Kind regards

Steve

 

[Steve]

Hi Claus, could the formula below be adapted to do the exact same
thing but this time to count the second from right number?

So as it stands if the number is

54321

and I use the formula it will return 5 in the new cell

but if it could be adapted so that the new cell formula was


=IF(ISERROR(CHOOSE(RIGHT(O1,1),5,3,2,1,1)),0,CHOOSE(RIGHT(O1,1),5,3,2,1,1))+
new formula

then the new cell would return 8 as the formula would look at the
right number and return 5 then the second right number and return a 3
added together to return 8

Is that possible ?

thanks

Steve

 

[Claus Busch]

Hi Steve,

Am Wed, 06 Jun 2012 17:42:17 +0100 schrieb Steve:

54321

then the new cell would return 8 as the formula would look at the
right number and return 5 then the second right number and return a 3
added together to return 8

try:
=IFERROR(CHOOSE(RIGHT(O1,1),5,3,2,1,1),0)+IFERROR(CHOOSE(MID(O1,LEN(O1)-1,1),5,3,2,1,1),0)


Regards
Claus Busch

 

[Steve]

Thank you again

regards

Steve

 

[Steve]

Sorry Claus, I have not given you enough information.

The exact example would be as follows:

the Cells in Column O could end in the following

9
8
7
6
5
4
3
2
1
P
F
S
B
R
U
No prev

Column O contains the data and Column GC would contain the new number.
So for example if

O1 = 4575 then GC1= 1

O2 = 35424 then GC2 = 1

O3 = 67543 then GC3 = 2

O4 = 7652 then GC4 = 3

O5 = 1111 then GC5 = 5

If any cells in column O end with anything other than 1,2,3,4,5 then
the relevant cell at GC = 0


I hope that is better Claus and thanks again

Steve

 

[Steve]

Hi Claus, I hope you are well.

I hope you dont mind another query

I have text ithin cells which have been formatted with font colour red
and I would like to count the number of cells with this format. It is
not conditionally formatted but done manually.

Is it possible to count the number of cells with this font formatting?

Thanks

Steve

 

[Claus Busch]

Hello Steve,

Am Thu, 07 Jun 2012 15:08:41 +0100 schrieb Steve:

I have text ithin cells which have been formatted with font colour red
and I would like to count the number of cells with this format. It is
not conditionally formatted but done manually.

Is it possible to count the number of cells with this font formatting?

You can filter for font color and then use SUBTOTAL
Your values in A1:A20
Filter for red color and then use
=SUBTOTAL(3,A1:A20)

Or use VBA to count your cells with red font color
Copy following code in a standard module
In the sheet use e.g. =ColorCount(A1:A20)

Function ColorCount(MyRange As Range) As Long
Dim rngC As Range

For Each rngC In MyRange
If rngC.Font.ColorIndex = 3 Then
ColorCount = ColorCount + 1
End If
Next
End Function


Please start a new thread for new questions.


Regards
Claus Busch

 

[Steve]

thanks Claus I will try both of these and will start a new thread as
advised

regards

Steve

 

[Zaidy036]

Hi Claus, I hope you are well.

I hope you dont mind another query

I have text ithin cells which have been formatted with font colour red
and I would like to count the number of cells with this format. It is
not conditionally formatted but done manually.

Is it possible to count the number of cells with this font formatting?

Thanks

Steve

free ASAP Utilities has this function