CODEWITHSUNDEEP
excel
Ssank
Ssank

Reputation: 3657

Making column names in excel

I have a table in excel like

CITY BIG_xxx BIG_aaa BIG_ggg SMALL_ccc SMALL_rrr SMALL_ddd

I want to rename the columns to 

CITY BIG_1 BIG_2 BIG_3 SMALL_1 SMALL_2 SMALL_3

How can I do this when there are a large number of columns with BIG_* and SMALL_*

Upvotes: 0

Views: 44

Answers (1)

JNevill
JNevill

Reputation: 50034

Assuming your columns start in A1, then in B2 you can use:

=IF(COUNTIF($A1:A1, LEFT(B1, FIND("_", B1)-1) & "*")>0, LEFT(B1, FIND("_", B1)-1) & "_" & COUNTIF($A1:A1, LEFT(B1, FIND("_", B1)-1) & "*") + 1, LEFT(B1, FIND("_", B1)-1) & "_1")

And copy across all the column fields.

The real heroes in this function are:

  1. LEFT(B1, FIND("_", B1)-1) which will get the string before the underscore.
  2. COUNTIF($A1:A1, LEFT(B1, FIND("_", B1)-1) & "*") Which counts how many columns previous to the column that is being renamed share the same string before the underscore using a COUNTIF() with a wildcard match.

In action: enter image description here

Upvotes: 2

Related Questions